Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete from multiple tables in a batch

Posted on 2010-08-23
7
Medium Priority
?
1,409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

722 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