Hello folks.
Can anyone tell me what is wrong with the following SQL statements?
The purpose purpose of the UPDATE is to promote one of the prod_cat_code values for a product that are stored in the product_category_xref table up to the product table (any value will be good - even NULL). So I have:
UPDATE p
SET p.prod_cat_code = (SELECT TOP(1) pcx.cat_code
FROM prod_category_xref pcx
WHERE pcx.prod_num = p.prod_num)
FROM product p;
After that, I want to get rid any records in product_category_xref which have the same prod_cat_code as the one now stored on the product record.
DELETE pcx
FROM [dbo].[product_category_xref] pcx
WHERE EXISTS (SELECT [dbo].[product] AS p
WHERE p.prod_num = pcx.prod_num
AND p.prod_cat_code = pcx.prod_cat_code);
However, when I execute these two statements I get errors for the DELETE:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.prod_num" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "p.prod_cat_code" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.product" could not be bound.
I don't understand why. I eventually changed the DELETE to
DELETE pcx
FROM product_category_xref pcx
JOIN product AS p
ON p.prod_num = pcx.prod_Num
AND p.prod_cat_code = pcx.prod_cat_code;
which accomplishes the same thing. But now I get an error on the UPDATE (which didn't throw an error before):
Msg 208, Level 16, State 1, Line 9
Invalid object name 'prod_category_xref'.
All I can say is 'huh?' I know that I can change the UPDATE to work too, but I don't see why the original statements do not.
Any ideas?