Loop through a table and delete rows in another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loop through a table and delete rows in another

Author

Topic

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-03-27 : 22:49:01

Hi Guy,

I have the following table - "GAorder_limitedbydatetest"

here is a screenshot of the table.
http://img687.imageshack.us/img687/6631/92509836.png

Each row represents a ticket to a performance. As you can see it has performancename and event_date.

Same performancename can have different "event_date"s.

I want to delete all the rows (tickets) that has been sold to the "performances + event_date" combinations that has less then 40 rows (tickets). (e.g. if performancename "ORIGIN OF STORY" on "2009-06-22 18:00:00" has less than 40 rows, i need to delete all of them).

I have made another table from this table called "GA_gone". I have taken all the performance, event_date combinations where the count(*) is lesser than 40.

Look at a screenshot - http://img534.imageshack.us/img534/6192/gagone.png.

It has 410 rows. I need to go through one by one and do the following manually for all 410 rows.:

Delete from GAorder_limitedbydatetes
where performancename = 'ORIGIN OF STORY' and event_date = '2009-06-22 18:00:00'

Instead can I write a T-SQL to loop through the table and delete from the main tickets table?

Thanks a lot for your time.

SHM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-28 : 02:37:29

you can. something like


DELETE t
FROM GAorder_limitedbydatetest t
INNER JOIN GA_gone g
ON g.performanceName = t.PerformanceName
AND g.event_date=t.EVENT_DATE

first do a select to check it returns only records you need to remove and then do actual delete

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shiyam198
Yak Posting Veteran

94 Posts

Posted - 2010-03-28 : 18:24:15

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 00:28:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources