Delete from multiple tables in a batch
Posted on 2010-08-23
I need to delete data from multiple tables in which the content of the other table defines what needs to be deleted from the table.
There are multiple tables
Every of these tables does contain a field ref_id and additional fields.
There is one kind of master table that contains for every entry in one of the other tables an entry that also contains the ref_id and additional a master_id.
Now I need to delete the entries of a specific master id from only some of the tables. The other shall be untouched.
I could delete the entries in T_One by
DELETE FROM T_ONE WHERE ref_id in (SELECT ref_id FROM t_master WHERE master_id = <value>)
I could also do it by
Delete FROM T_One o INNER JOIN t_master m on o.ref_id = m.ref_id
This works. But now I also want to delete the entries of the master table for which I deleted the entries in T_one.
Here I stuck now because I do not know which ref_id's I deleted before.
I need to do it in a standard sql-batch file.
Is there an easy way to get this done?
The Server is Sybase ASE (T-Sql is similar to the MS Sql dialect)
Thanks in advance