SQL UPDATE和DELETE，别名不起作用 [英] SQL UPDATE and DELETE with aliases not working

问题描述

大家好。



谁能告诉我以下SQL语句有什么问题？



UPDATE的目的是为了将product_category_xref表中存储的产品的prod_cat_code值之一提升到产品表（任何值都将是好 - 甚至是空的）。所以我有：

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; 

之后，我想删掉product_category_xref中与产品记录中存储的prod_cat_code相同的任何记录。

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);

然而，当我执行这两个语句我得到DELETE的错误：

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.

我不明白为什么。我最终将DELETE更改为

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;

完成同样的事情。但是现在我在UPDATE上遇到错误（之前没有抛出错误）：

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'.

我只能说'嗯？'我知道我可以更改UPDATE也可以工作，但我看不到为什么原始陈述没有。

任何想法？

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

请参阅 digimanus [ ^ ]获得正确更新的答案语句。

如果删除语句出错，则需要检查 ON CASCADE DELETE已开启 [ ^ ]。

See digimanus[^] answer to get proper update statement.
If error occurs on delete statement, you need to check if ON CASCADE DELETE is on[^].

这篇关于SQL UPDATE和DELETE，别名不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！