Procedure for deleting records

[padmaneha]

Hi

I have created two tables 'TrainsMaster' & 'TransArrvlDepi nfo'

Columns which I have created in 'TrainsMaster' are 'trainName,Trai nNo, StartStaionId, & EndstationId'

Columns which I have created in ''TransArrvlDep info' are 'Stationcode, TrainNo, Arrvaltime,Dept time'

I have to delete few trains from 'TrainsMaster' for which the Startstationid or Endstationid are nulls which comes to 50 records

The above said null valued trains should be removed form ''TransArrvlDep info' table as well.

For example I have the following trains to be deleted from 'TrainsMaster'

Train Name Train No Startstionid endstationid
Yeshvantpur - Guwahati Express 201 NULL GHY
Jammutawi AC Special 905 NULL JAT
Amritsar AC Special 951 NULL ASR
Delhi - Howrah Special 232 NULL HWH

When I check the above trains in 'TransArrvlDepi nfo' table there are lot of trains which passes through the staioncode 'ghy'

For example 'GHY' is the station code in which lots of trains run through. I need a procedure for deleting the above said trains with the same station code. i.e. I should be deleting only train no 201 for which the station code is 'ghy' similarly the above said trains should be deleted from 'TransArrvlDepi nfo' table with their corresponding stationcodes.

Please give the procedure for deleting multiple records

[deepuv04]

Hi,
First you need to delete records from table TransArrvlDepin fo, and then delete from table TrainsMaster

use the following code:
[code=sql]
CREATE PROCEDURE DeleteTrainInfo
AS
BEGIN
-- Delete records from TransArrvlDepin fo
DELETE FROM TransArrvlDepin fo
FROM TransArrvlDepin fo INNER JOIN
TrainsMaster ON TrainsMaster.Tr ainNo = TransArrvlDepin fo.TrainNo
WHERE (TrainsMaster.S tartstationid IS NULL OR TrainsMaster.En dstationid IS NULL)

-- Delete records from TrainsMaster
DELETE FROM TrainsMaster
FROM TrainsMaster
WHERE (TrainsMaster.S tartstationid IS NULL OR TrainsMaster.En dstationid IS NULL)
END
[/code]
To delete a specific train info pass train no as parameter and use it in where clause.
Thanks

[padmaneha]

Hi Thanks for your help.

Hi,
First you need to delete records from table TransArrvlDepin fo, and then delete from table TrainsMaster

use the following code:
[code=sql]
CREATE PROCEDURE DeleteTrainInfo
AS
BEGIN
-- Delete records from TransArrvlDepin fo
DELETE FROM TransArrvlDepin fo
FROM TransArrvlDepin fo INNER JOIN
TrainsMaster ON TrainsMaster.Tr ainNo = TransArrvlDepin fo.TrainNo
WHERE (TrainsMaster.S tartstationid IS NULL OR TrainsMaster.En dstationid IS NULL)

-- Delete records from TrainsMaster
DELETE FROM TrainsMaster
FROM TrainsMaster
WHERE (TrainsMaster.S tartstationid IS NULL OR TrainsMaster.En dstationid IS NULL)
END
[/code]
To delete a specific train info pass train no as parameter and use it in where clause.
Thanks