Solved

Delete from multiple tables in a batch

Posted on 2010-08-23
7
1,250 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 21

Expert Comment

by:mastoo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now