• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1497
  • Last Modified:

Delete from multiple tables in a batch

Hi,

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.


Sample:

There are multiple tables

T_One
T_Two
T_Three

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
0
HugoHiasl
Asked:
HugoHiasl
1 Solution
 
mastooCommented:
Select the ref_id of the child tables into a temp table before you delete them, then delete them, then delete those from the master table.  All within a transaction.
0
 
aflockhartCommented:
Depends on your data structure, but you might be able to identify the master records that you want to delete, by looking for 'orphaned' entries that no longer have a match:  

select *    -- ( or delete, once you have tested !)
from t_master m left join t_one t on m.ref_id=t.ref_id
where t.ref_id is null
and m.master_id = <value>

(but note that this would also delete any existing 'orphaned' records as well.
0
 
HugoHiaslAuthor Commented:
I tried it with a temp table. But if I did it in a batch the database responded that CREATE TABLE is not allowed in multiple command batch statements.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mastooCommented:
I'm not familiar with Sybase but in MS you could also try Select Into, or do your create table outside of the transaction and have a delete from that table as the first statement of the transaction maybe.
0
 
aflockhartCommented:
Or if you can amend the table structure; you could set up a field [ToBeDeleted] in each table; then the process could be:

1: UPDATE T_One SET [ToBeDeleted]='y' where ... <conditions>
2: DELETE FROM T_MAster where   <conditions> ....  AND t_one.ToBeDeleted='y'
3: DELETE FROM T_One where t_one.ToBeDeleted='y'
0
 
manekshCommented:
Hi,

 Are you deleting for one ref id at a time? If yes you could store the id in a variable and delete.
 OR
 use a hash table to store deleted rows
OR
create a trigger on the T_One table to delete the master table on delete of the child.You might need extra conditions in the trigger to identify the delete. (Wouldn't reommend this one though )

Regards
Maneksh

 
0
 
Joe WoodhousePrincipal ConsultantCommented:
Temp table creation doesn't have to be in the transaction, just the two deletes.

select [...] into #temp_delete where [...]
begin tran
delete child from child, #temp_delete where child.ID = #temp_delete.ID
delete parent from parent, #temp_delete where parent.ID = #temp_delete.ID
[error checking]
commit tran
go

Depending on the number of rows involved, you may also want to create an index on the ID column of the temp table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now