delete不起作用 nsis_SQL UPDATE和DELETE，别名不起作用

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?

解决方案Look here[^]. Example can be found

See digimanus[^] answer to get proper update statement.

If error occurs on delete statement, you need to check if ON CASCADE DELETE is on[^].