有可能的方法吗:
我想根据ORDER BY位置更新字段"rank"。
例如:(伪代码(
If id order by place = 1 then update rank field to place were id=get id
rank place id
1 1 5 PC
2 2 8 MAC
这可能吗?
类似的东西?
UPDATE tbl_name
SET rank = 1
WHERE id = (
SELECT id
WHERE condition
ORDER BY place DESC
LIMIT 1
)
或者从你的评论(我认为MySQLhttp://dev.mysql.com/doc/refman/5.0/en/update.html):
UPDATE tbl_name
SET rank = 10
WHERE id = 9
ORDER BY wins DESC
LIMIT 1
您总是可以执行SELECT来检查这些记录是否也是您想要更新的:
SELECT *
FROM tbl_name
WHERE id = (
SELECT id
WHERE condition
ORDER BY place DESC
LIMIT 1
)
或
SELECT *
FROM tbl_name
WHERE id = 9
ORDER BY wins DESC
LIMIT 1
一些RDBMS在查询中有一个ROWNUM伪列,您可以使用它。您没有指定正在使用的数据库,例如Oracle有此数据库。