Simple Update using three tables. . .

Question

Simple Update using three tables. . .

donato1026

SSCrazy

Points: 2765
More actions
May 20, 2010 at 9:40 am

#224803

I know this is simple, but it is not working. . . I was getting the obvious error when using the '=' operator, but when using IN, it doesn't work either:
update ProductVariant
set ProductVariant.Price IN (select ret_prc from itm i, product p, ProductVariant pv
where i.itm_cd = p.sku and p.ProductID = pv.ProductID)
I need to update the Price in the ProductVariant table. . . However, I need to join the ProductVariant table and the Product table on ProductID. BUT - the price is in a table called ITM, which needs to be joined to the Product table on SKU.
Any help?
Thanks in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Donato this is how i usually update a table from multiple sources...note the commented SELECT statement so i can run the bottom portion to test before and after:

SET XACT_ABORT ON

BEGIN TRAN

update pv

SET pv.Price = ret_prc

--SELECT pv.Price,ret_prc,*

FROM ProductVariant pv

INNER JOIN product p

ON p.ProductID = pv.ProductID

INNER JOIN itm i

ON i.itm_cd = p.sku

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

donato1026 SSCrazy Points: 2765 More actions · Answer 2

Lowell (5/20/2010)
Donato this is how i usually update a table from multiple sources...note the commented SELECT statement so i can run the bottom portion to test before and after:
SET XACT_ABORT ON
BEGIN TRAN
update pv
SET pv.Price = ret_prc
--SELECT pv.Price,ret_prc,*
FROM ProductVariant pv
INNER JOIN product p
ON p.ProductID = pv.ProductID
INNER JOIN itm i
ON i.itm_cd = p.sku

Unbelievable. . . Thank you!!! . . . again. 😉

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

donato1026 (5/20/2010)
Lowell (5/20/2010)
Donato this is how i usually update a table from multiple sources...note the commented SELECT statement so i can run the bottom portion to test before and after:
SET XACT_ABORT ON
BEGIN TRAN
update pv
SET pv.Price = ret_prc
--SELECT pv.Price,ret_prc,*
FROM ProductVariant pv
INNER JOIN product p
ON p.ProductID = pv.ProductID
INNER JOIN itm i
ON i.itm_cd = p.sku
Unbelievable. . . Thank you!!! . . . again. 😉

Just to be sure... as Lowell did in the above code, if you have a join in the FROM clause of an UPDATE, one of the things that absolutely MUST be in the FROM clause is the table you're updating. It is possible to write an update without that being true but such an UPDATE will frequently lose its mind and slam several CPU's into the wall for hours trying to do a simple update that should only take several seconds to accomplish.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)