update not working - SQL Server Forums

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

SQL Server Development (2000)

update not working

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-07 : 05:51:12

Hi there guys can anyone advise my why these following updates dont update any rows in my table although they are syntaxically correct and there are several rows with storenum 181, 113 in itemsales_test table but they are not getting updated :

UPDATE itemsales_test
SET itemsales_test.supplierid = case when itemsales_test.storenum in (113,181) then RetailChoiseProduct.JsySfwySuppCode end
FROM itemsales_test
INNER JOIN RetailChoiseProduct ON itemsales_test.prodcode = RetailChoiseProduct.productcode

UPDATE itemsales_test
SET itemsales_test.supplierid = RetailChoiseProduct.JsySfwySuppCode
FROM itemsales_test INNER JOIN RetailChoiseProduct ON itemsales_test.prodcode = RetailChoiseProduct.productcode
WHERE itemsales_test.storenum in (113,181)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 06:00:51

Try this. Any records return ?

SELECT *
FROM itemsales_test INNER JOIN RetailChoiseProduct ON itemsales_test.prodcode = RetailChoiseProduct.productcode
WHERE itemsales_test.storenum in (113,181)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-07 : 06:01:16

Well the first will update all rows that match the filter not just (113,181), if they are not (113,181) they will be updated to null.

As it sounds like that's not happening I guess the join doesn't return anything.
try
select *
FROM itemsales_test
INNER JOIN RetailChoiseProduct
ON itemsales_test.prodcode = RetailChoiseProduct.productcode

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-08-07 : 06:24:05

Guys you are right my join is returning nothing.

UPDATE itemsales_test
SET itemsales_test.supplierid = RetailChoiseProduct.JsySfwySuppCode
FROM itemsales_test INNER JOIN RetailChoiseProduct ON itemsales_test.prodcode = left(RetailChoiseProduct.productcode,6)
WHERE itemsales_test.storenum in (113,181)

thanks for putting me on the right track.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources