April 23, 2010 at 11:46 am
I'm writing a script that select the rows to be deleted based on an inner join.
delete t2 from #temp d
inner join table1 t1 with(nolock)
on d.id= t1.id
inner join table2 t2 with(nolock)
on t1.user = t2.user
How can I display which rows were deleted by the previous script?
thanks,
April 23, 2010 at 12:02 pm
Just asking for clarification: are you running SQL2000? (that's the forum you posted in).
If yes I think one of the few ways in 2K to do it is to have an insert into #table statement first and do the delete based on that table.
If you're on 2K5 or above you could use the OUTPUT clause.
April 23, 2010 at 12:03 pm
Before deletion you can insert the data you want to delete in a new #temp table using the sub query below. Eg.
create table #tempDEL ('id','data_type',...)
...THEN
insert into #tempDEL (select t1.id from table1 t1 inner join table2 t2 on t1.user = t2.user)
...THEN
delete d.id
from #temp d
where d.id in (select id from #tempDEL)
#tempDEL is holding what you just have deleted so you can just query it again.
Hope this helps!... koncentrix
April 23, 2010 at 12:07 pm
That works pretty well, how about this case:
delete t2 from #temp d
inner join table1 t1 with(nolock)
on d.id= t1.id and d.id2 = t1.id2
inner join table2 t2 with(nolock)
on t1.user = t2.user
April 23, 2010 at 12:19 pm
April 23, 2010 at 12:28 pm
Koncentrix (4/23/2010)
Another thing is ...DELETE #temp
OUTPUT DELETED.*;
in the following DELETE statement returns all columns deleted from the #temp table
Please note that this is an 2000 forum and the OUTPUT clause was introduced in 2K5.
Therefore, I asked for clarification in my prev. post.
April 23, 2010 at 12:30 pm
thank you all,
you could be more helpful
appreciate it,
April 23, 2010 at 12:34 pm
April 23, 2010 at 12:46 pm
ramirez.sebastian (4/23/2010)
thank you all,you could be more helpful
appreciate it,
What help did you expect? You posted a vague question. So you got vague answers. And you haven't even answered my question regarding the version you're using, yet...
April 23, 2010 at 12:49 pm
I was using sql server 2000, but it doesn't hurt to learn the futures of 2005 and above 🙂
April 23, 2010 at 12:57 pm
Judging by the tone of the posts, I think he meant 'you couldn't have been more helpful' . It was a sincere 'thank you', not a complaint.
April 23, 2010 at 1:01 pm
For better answers to your questions, please read and follow the instructions given in the first article I reference below in my signature block regarding "asking for help." Also, be sure to provide the expected results you are looking for (and not just a description, but something we can actually compare to our own results).
Please remember, most of us are up-paid volunteers giving of our own time and energy to assist others in the SQL Server Community.
April 23, 2010 at 2:00 pm
Appreciate you suggestion. Hope to get to the point where can be helping other people too.
Cheers all 😎
April 23, 2010 at 2:04 pm
Lynn Pettis (4/23/2010)
For better answers to your questions, please read and follow the instructions given in the first article I reference below in my signature block regarding "asking for help." Also, be sure to provide the expected results you are looking for (and not just a description, but something we can actually compare to our own results).Please remember, most of us are up-paid volunteers giving of our own time and energy to assist others in the SQL Server Community.
Most of us?? Who the heck is paid besides Steve and Red-Gate?
April 23, 2010 at 2:53 pm
Ninja's_RGR'us (4/23/2010)
Lynn Pettis (4/23/2010)
For better answers to your questions, please read and follow the instructions given in the first article I reference below in my signature block regarding "asking for help." Also, be sure to provide the expected results you are looking for (and not just a description, but something we can actually compare to our own results).Please remember, most of us are up-paid volunteers giving of our own time and energy to assist others in the SQL Server Community.
Most of us?? Who the heck is paid besides Steve and Red-Gate?
Well, we do get paid for original articles we write, but besides that we don't. 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply