Solved

Delete from multiple tables in a batch

Posted on 2010-08-23
7
1,313 Views
Last Modified: 2012-05-10
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
Comment
Question by:HugoHiasl
7 Comments
 
LVL 21

Expert Comment

by:mastoo
ID: 33501476
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
 
LVL 17

Expert Comment

by:aflockhart
ID: 33501716
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
 
LVL 12

Author Comment

by:HugoHiasl
ID: 33506162
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 21

Expert Comment

by:mastoo
ID: 33506213
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
 
LVL 17

Expert Comment

by:aflockhart
ID: 33508570
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
 
LVL 2

Expert Comment

by:maneksh
ID: 33509847
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
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 33529931
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question