Creating a complex update statement

Question

Creating a complex update statement

mike 57299

SSCrazy

Points: 2529
More actions
March 15, 2010 at 10:03 pm

#221912

Hi.
How would I combine the following statements into one:
SELECT customer.customer_id
INTO #C
FROM Customer
WHERE Customer.active = 1 AND Customer.prospect = 0
UPDATE Orders SET hold = 1
WHERE ship is null AND order_type = 'ORDER'
AND customer_id NOT IN (Select customer_id FROM #C)
Thanks,
Mike

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 1

What is the purpose of creating the temporary table #C? Are you using it anywhere else other than your second statement?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mike 57299 SSCrazy Points: 2529 More actions · Answer 2

Yes, I use it to do an insert into another table as well.

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 3

Then, you can't replace your above statements with a single statement.

But i can give you a better way of doing the update without using the #C and which will not give you problems if you have NULL values as your customer_id

UPDATEO

SEThold = 1

FROMOrders O

WHEREship is null AND order_type = 'ORDER'

AND NOT EXISTS( SELECT * FROM Customer C WHERE C.active = 1 AND C.prospect = 0 AND O.customer_id = C.customer_id )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

mike 57299 SSCrazy Points: 2529 More actions · Answer 4

Kingston D.

But i can give you a better way of doing the update without using the #C and which will not give you problems if you have NULL values as your customer_id
UPDATEO
SEThold = 1
FROMOrders O
WHEREship is null AND order_type = 'ORDER'
AND NOT EXISTS( SELECT * FROM Customer C WHERE C.active = 1 AND C.prospect = 0 AND O.customer_id = C.customer_id )

Is this faster?

Mike

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 5

Not sure about the performance aspect. But this will help you avoid certain problems that are mentioned in the following link

http://qa.sqlservercentral.com/blogs/never_say_never/archive/2010/1/28/in-and-not-in.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

tosscrosby SSCoach Points: 18045 More actions · Answer 6

mike 57299 (3/15/2010)
Kingston D.
But i can give you a better way of doing the update without using the #C and which will not give you problems if you have NULL values as your customer_id
UPDATEO
SEThold = 1
FROMOrders O
WHEREship is null AND order_type = 'ORDER'
AND NOT EXISTS( SELECT * FROM Customer C WHERE C.active = 1 AND C.prospect = 0 AND O.customer_id = C.customer_id )
Is this faster?
Mike

This will be a bit more efficient:

UPDATEO

SEThold = 1

FROMOrders O

WHEREship is null AND order_type = 'ORDER'

AND NOT EXISTS( SELECT 1 FROM Customer C WHERE C.active = 1 AND C.prospect = 0 AND O.customer_id = C.customer_id )

[/quote]

Maybe?

update O

set hold = 1

from Orders O

inner join Customer C

on O.customer_id = C.customer_id

Where (C.active <> 1 AND C.prospect <> 0)

With proper indexes (customer_id on both tables), the execution plan seems to favor this solution.

-- You can't be late until you show up.