SQL QUERY - UPDATE STATEMENT WITH JOIN WITH OTHER TABLE

Question

Here is the query that I have come up with but seems not working right.

UPDATE ItemDynamic
SET TaxID=8
FROM ItemDynamic
INNER JOIN Item
ON Item.ID = ItemDynamic.ItemID
WHERE Item.DateCreated>'2013-06-18' and StoreID=11 or StoreID=1 or StoreID=2 or StoreID=3

DateCreated column is not in the ItemDynamic, this column is in Item table. So I join Item table and ItemDynamic table. The problem is, when I tried to update more stores same time, it updates the whole item. But when I update only one store, it updates fine. Is there any reason why?

Thanks,

sounds like you just need brackets WHERE Item.DateCreated>'2013-06-18' and (StoreID=11 or StoreID=1 or StoreID=2 or StoreID=3) — T I
– T I, Commented Jun 19, 2013 at 21:14

Fabio · Accepted Answer · 2013-06-19 21:15:53Z

1

You have an error in your query, and your OR clause should be surrounded with round brackets

UPDATE ItemDynamic a 
INNER JOIN Item B
ON b.ID = a.ItemID 
SET a.TaxID=8 
WHERE b.DateCreated>'2013-06-18' 
AND (b.StoreID=11 OR b.StoreID=1 OR b.StoreID=2 OR b.StoreID=3)

I assumed StoreID and DateCreated are in table Item.

answered Jun 19, 2013 at 21:15

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2502947 Over a year ago

INNER JOIN giving me an error. But I used a_horse_with_no_name gave me the correct answer! Thanks!

Barmar · Accepted Answer · 2013-06-19 21:52:46Z

1

When you want to test a field against multiple values, I find IN to be clearer than a bunch of OR tests. And it avoids the AND/OR precedence problem that you ran into.

UPDATE ItemDynamic
INNER JOIN Item
SET TaxID=8
ON Item.ID = ItemDynamic.ItemID
WHERE Item.DateCreated>'2013-06-18'
AND StoreID IN (11, 1, 2, 3)

answered Jun 19, 2013 at 21:52

Barmar

789k57 gold badges554 silver badges669 bronze badges

1 Comment

Barmar Over a year ago

What was the error? And if you got an answer, you should post it for the benefit of others.

Collectives™ on Stack Overflow

SQL QUERY - UPDATE STATEMENT WITH JOIN WITH OTHER TABLE

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related