Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete from multiple tables in a batch

Posted on 2010-08-23
7
Medium Priority
?
1,433 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

971 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