Procedure for deleting records

[padmaneha]

Hi

I have created two tables 'TrainsMaster' & 'TransArrvlDepinfo'

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

Columns which I have created in ''TransArrvlDepinfo' are 'Stationcode, TrainNo, Arrvaltime,Depttime'

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 ''TransArrvlDepinfo' 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 'TransArrvlDepinfo' 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 'TransArrvlDepinfo' table with their corresponding stationcodes.

Please give the procedure for deleting multiple records

[deepuv04]

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

use the following code:

CREATE PROCEDURE DeleteTrainInfo
AS
BEGIN
    -- Delete records from  TransArrvlDepinfo
    DELETE FROM TransArrvlDepinfo
    FROM TransArrvlDepinfo INNER JOIN
         TrainsMaster ON TrainsMaster.TrainNo = TransArrvlDepinfo.TrainNo
    WHERE (TrainsMaster.Startstationid IS NULL OR TrainsMaster.Endstationid IS NULL)
 
    -- Delete records from TrainsMaster
    DELETE FROM TrainsMaster
    FROM TrainsMaster
    WHERE (TrainsMaster.Startstationid IS NULL OR TrainsMaster.Endstationid IS NULL)
END
 

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 TransArrvlDepinfo, and then delete from table TrainsMaster

use the following code:

CREATE PROCEDURE DeleteTrainInfo
AS
BEGIN
    -- Delete records from  TransArrvlDepinfo
    DELETE FROM TransArrvlDepinfo
    FROM TransArrvlDepinfo INNER JOIN
         TrainsMaster ON TrainsMaster.TrainNo = TransArrvlDepinfo.TrainNo
    WHERE (TrainsMaster.Startstationid IS NULL OR TrainsMaster.Endstationid IS NULL)
 
    -- Delete records from TrainsMaster
    DELETE FROM TrainsMaster
    FROM TrainsMaster
    WHERE (TrainsMaster.Startstationid IS NULL OR TrainsMaster.Endstationid IS NULL)
END
 

To delete a specific train info pass train no as parameter and use it in where clause.
Thanks