为什么在删除和更新查询中,EXISTS语句的工作方式不同?

huangapple go评论130阅读模式
英文:

Why EXISTS statement works differently in delete and update query?

问题

请告诉我在EXISTS语句中我漏掉了什么。我正在遵循这个网站。https://www.geeksforgeeks.org/sql-exists/

我有两个表。Customers和Orders。

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

这是删除查询。

DELETE 
FROM Orders
WHERE EXISTS (SELECT *
              FROM Customers
              WHERE Customers.customer_id = Orders.c_id
              AND Customers.lname = '4');

起初,我认为这个删除查询会删除整个表,因为EXISTS返回TRUE(我的意思是,存在一个具有customers.lname = '4'的行)。但是这是结果:

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

子查询返回True吗??然后带有lname = 4的行被删除了。
所以我期望更新查询将更新带有customer_id = 401的行,就像它刚刚为删除操作做的那样。
但是这是结果:

UPDATE Customers
SET lname = 'aaa'
WHERE EXISTS (SELECT * 
              FROM Customers
              WHERE customer_id = 401);

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

整个表被更新,就好像EXISTS只是返回了True。这真的让我困惑。为什么EXISTS对删除和更新起作用不同?还是我做错了什么(我认为是这样,但我自己搞不清楚)?更新查询和删除查询发生了什么?

英文:

Please tell me what am I missing with EXISTS statement. I'm following this website. https://www.geeksforgeeks.org/sql-exists/

I have two tables. Customers and Orders.

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

and here is delete query.

DELETE 
FROM Orders
WHERE EXISTS (SELECT *
              FROM Customers
              WHERE Customers.customer_id = Orders.c_id
              AND Customers.lname = '4');

At first, I thought this delete query will delete the whole table as EXISTS returns TRUE(I mean, there IS a row with customers.lname = '4') But here is the result:

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

subquery returns True?? and the row with lname = 4 is deleted.
So I expected update query will update the row with customer_id = 401 like it just did for delete.
But here is the result :

UPDATE Customers
SET lname = 'aaa'
WHERE EXISTS (SELECT * 
              FROM Customers
              WHERE customer_id = 401);

为什么在删除和更新查询中,EXISTS语句的工作方式不同?

Whole table is updated like EXISTS just returns True. This really confuses me. Why EXISTS works differently for delete and update? Or did I do something wrong(I think so, but I can't figure it out myself)? What happened to update query and delete query?

答案1

得分: 1

在第一个查询中,您对每一行执行存在性检查,因为您引用了源表(orders.c_id):

SELECT *
FROM Customers
WHERE Customers.customer_id = Orders.c_id
AND Customers.lname = '4'

而在第二个查询中,您没有进行这样的操作,只是有一个真或假的条件,起到了条件成立时的作用。

英文:

In the first query you are performing the exists check for each row as you are referring the source table (orders.c_id):

SELECT *
FROM Customers
WHERE Customers.customer_id = Orders.c_id
AND Customers.lname = '4'

while in the second query you are not and you have just a true or false condition acting as if block.

huangapple
  • 本文由 发表于 2023年3月9日 14:34:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75681166.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定