假设我有以下代码:
$ids = execute_query("SELECT id FROM table WHERE field = 'value' ORDER BY order_field");
$query = "UPDATE table SET increment = CASE";
for ($i = 0; $i <= sizeof($ids); $i++) {
$query .= " WHEN id = " . $ids[$i] . " THEN " . $i;
}
$query .= " END WHERE field = 'value'";
然后执行该查询。
是否有一种方法将SELECT
和UPDATE
组合成一个查询,有效地实现完全相同的事情?
在子查询同一表时更新表是不可能的:http://dev.mysql.com/doc/refman/5.6/en/update.html "目前,您不能在子查询中更新表并从同一表中进行选择。"
我没有在这里运行,但据我所知,这是你想要的:
SET @ordering = 0;
UPDATE
table SET increment = (@ordering := @ordering + 1)
WHERE
field = 'value'
ORDER BY
order_field;
您可以在单独的查询中执行SET
和UPDATE
,只要您不重新连接数据库。
这可能是你想要的:
update table
set increment = (select cnt
from (select count(*) as cnt
from table t
where field = 'value' and t.id <= table.id
) a
)
where field = 'value';
SET @ordering = 100000;
UPDATE candidate
SET regno = (@ordering := @ordering + 1)
WHERE
year = 2014 AND
confirm = 1 AND
e_stat = 1
ORDER BY centre_code, `name`;