英文:
Why EXISTS statement works differently in delete and update query?
问题
请告诉我在EXISTS语句中我漏掉了什么。我正在遵循这个网站。https://www.geeksforgeeks.org/sql-exists/
我有两个表。Customers和Orders。
这是删除查询。
DELETE
FROM Orders
WHERE EXISTS (SELECT *
FROM Customers
WHERE Customers.customer_id = Orders.c_id
AND Customers.lname = '4');
起初,我认为这个删除查询会删除整个表,因为EXISTS返回TRUE(我的意思是,存在一个具有customers.lname = '4'的行)。但是这是结果:
子查询返回True吗??然后带有lname = 4
的行被删除了。
所以我期望更新查询将更新带有customer_id = 401
的行,就像它刚刚为删除操作做的那样。
但是这是结果:
UPDATE Customers
SET lname = 'aaa'
WHERE EXISTS (SELECT *
FROM Customers
WHERE customer_id = 401);
整个表被更新,就好像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.
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:
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);
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论