better way to do this query? nested select IN stmt

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 2008 Forums

Transact-SQL (2008)

better way to do this query? nested select IN stmt

Author

Topic

merk
Starting Member

4 Posts

Posted - 2011-04-07 : 15:59:43

[code] UPDATE Ticket
SET problem = 'PostDate Sale: Approved', LastUpdated = GETDATE()
WHERE ticket_number IN
(SELECT ticket_number FROM Sale
WHERE saleid IN
(
SELECT msp.saleid
FROM Membership_Sales_Postdate msp, PostDateProcessCCTemp pdp
WHERE (msp.leadNumber = pdp.id OR msp.CustomerID = pdp.id)
AND pdp.result = 'APPROVE'
)
)[/code]

basically, i'm selected a list of ID's from table, then selecting another set of ID's based off that first set, and then using that 2nd set of ID's to update a 3rd table.

This seems horribly ugly to me. Would using a join be the optimal solution here? Besides this looking ugly to me, are there performance issues doing it this way?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-07 : 16:23:20

this might work, test it out in dev


UPDATE tgt
   SET problem = 'PostDate Sale: Approved', LastUpdated = GETDATE()
  FROM Ticket tgt

 inner join Sale s
    on tgt.ticket_number = s.ticket_number
    
 inner join (
 
 SELECT msp.saleid 
   FROM Membership_Sales_Postdate msp, PostDateProcessCCTemp pdp
  WHERE (msp.leadNumber = pdp.id OR msp.CustomerID = pdp.id)
	AND pdp.result = 'APPROVE'
 ) oogly
    on oogly.saleid = s.saleid

also if you put a default constraint on LastUpdated you will not need to do LastUpdated = GETDATE()
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources