在 select 语句中更新表 [英] Updating a table within a select statement

问题描述

有什么方法可以更新 mysql 选择查询的 select_expr 部分中的表.这是我想要实现的目标的示例:

Is there any way to update a table within the select_expr part of a mysql select query. Here is an example of what I am trying to achieve:

SELECT id, name, (UPDATE tbl2 SET currname = tbl.name WHERE tbl2.id = tbl.id) FROM tbl;

这让我在 mysql 中出现错误，但我不明白为什么只要我不更改 tbl，就不可能实现.

This gives me an error in mysql, but I dont see why this shouldn't be possible as long as I am not changing tbl.

我将澄清为什么我不能为此使用普通构造.

I will clarify why I cant use an ordinary construct for this.

这是我正在处理的问题的更复杂示例:

Here is the more complex example of the problem which I am working on:

SELECT id, (SELECT @var = col1 FROM tbl2), @var := @var+1, 
(UPDATE tbl2 SET col1 = @var) FROM tbl WHERE ...

所以我基本上处于这样一种情况，我在 select 语句期间增加一个变量，并希望在我选择行时反映这种变化，因为我在执行期间使用了这个变量的值.此处给出的示例可能可以通过其他方式实现，但是由于不必要的代码太多，我不会在此处发布的真实示例需要此功能.

So I am basically in a situation where I am incrementing a variable during the select statement and want to reflect this change as I am selecting the rows as I am using the value of this variable during the execution. The example given here can probably be implemented with other means, but the real example, which I wont post here due to there being too much unnecessary code, needs this functionality.

推荐答案

如果您的目标是每次查询 tbl1 时都更新 tbl2，那么最好的方法是创建一个存储过程来完成并将其包装在一个事务，如果需要原子性，可能会改变隔离级别.

If your goal is to update tbl2 every time you query tbl1, then the best way to do that is to create a stored procedure to do it and wrap it in a transaction, possibly changing isolation levels if atomicity is needed.

您不能在选择中嵌套更新.

You can't nest updates in selects.

这篇关于在 select 语句中更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！