如何使用多表语法进行更新？

发布于 2024-12-28 17:33:48 字数 530 浏览 1 评论 0原文

如何使用 SELECT 的结果来确定后续 UPDATE 中更改的字段。（在复合语句中有效地将 SELECT 和 UPDATE 链接在一起）。

这是我在 SQL 中尝试做的事情：

SELECT id_of_record_in_table_B 
FROM table_A 
WHERE table_A.id_of_record_in_table_B = 36;

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_B.id = id_of_record_in_table_B --from the SELECT above)

区分大小写的匹配与问题并不真正相关，但我已经将其包含在内，因为它实际上是我需要做的。我试图解决的问题是如何将从 SELECT 返回的值“传递”到 UPDATE 中。

原文

How can I use the result of a SELECT to determine the fields changed in a subsequent UPDATE. (Effectively chaining the SELECT followed by the UPDATE together in a compound statement).

Here is what I'm trying to do in SQL:

SELECT id_of_record_in_table_B 
FROM table_A 
WHERE table_A.id_of_record_in_table_B = 36;

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_B.id = id_of_record_in_table_B --from the SELECT above)

The case sensitive match is not really relevant to the problem but I've included it because it is actually what I need to do. The problem I am trying to work out is how to "pass" the value returned from the SELECT into UPDATE.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾｀听者〃 2025-01-04 17:33:48

您可以在同一个查询中使用它，例如

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_b.id = (SELECT id_of_record_in_table_B 
                  FROM table_A 
                  WHERE table_A.id_of_record_in_table_B = 36)

You could it in the same query such as

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_b.id = (SELECT id_of_record_in_table_B 
                  FROM table_A 
                  WHERE table_A.id_of_record_in_table_B = 36)

回复收藏 0 原文

旧竹 2025-01-04 17:33:48

这可以称为使用多表更新语法，从而避免子查询。此语法的唯一问题是不能使用 order by 和 limit 子句

 UPDATE table_B as tb,table_A as ta 
 SET tb.string_field = 'UPPERCASE'  

 WHERE BINARY tb.string_field LIKE '%lowercase' 
 AND tb.id = id_of_record_in_table_B
 AND tA.id_of_record_in_table_B = 36

This can be called update with multi table syntax thus avoiding subquery. The only problem with this syntax is you cannot use order by and limit clause

 UPDATE table_B as tb,table_A as ta 
 SET tb.string_field = 'UPPERCASE'  

 WHERE BINARY tb.string_field LIKE '%lowercase' 
 AND tb.id = id_of_record_in_table_B
 AND tA.id_of_record_in_table_B = 36

回复收藏 0 原文

咿呀咿呀哟 2025-01-04 17:33:48

您可以使用嵌套的 select 语句。

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_B.id IN (SELECT id_of_record_in_table_B 
                   FROM table_A 
                   WHERE table_A.id_of_record_in_table_B = 36);

You could use a nested select statement.

UPDATE table_B SET string_field = 'UPPERCASE' 
WHERE BINARY table_B.string_field LIKE '%lowercase' 
AND table_B.id IN (SELECT id_of_record_in_table_B 
                   FROM table_A 
                   WHERE table_A.id_of_record_in_table_B = 36);

回复收藏 0 原文

~没有更多了~