SQL ...如何用同一表中其他行的数据更新行? [英] SQL... How to update rows with data from other rows in the same table?
问题描述
我正在使用MySQL服务器,并且我有一个表,其中某些行缺少数据.我想使用其他行中的信息来更新行.我的桌子看起来像:
I'm using MySQL server and I have a table where some rows are missing data. I would like to update the rows using information from other rows. My table looks something like:
id,signin,deviceId,deviceModel
1,2010-10-12,9ABC9,
2,2010-10-12,3E44F,
3,2010-10-13,D3453,
4,2010-10-14,D3453,
5,2010-10-14,D3453,HW1
6,2010-10-12,3E44F,HW2
7,2010-10-12,9ABC9,HW1
对于前几个条目,deviceModel字段为空.我想使用在同一表的其他行中为deviceId找到的deviceModel更新此值.在上面的示例中,第1行应具有deviceModel = HW1,第2行应具有deviceModel = HW2,依此类推.
For the first few entries, the deviceModel field is empty. I would like to update this value using the deviceModel found for the deviceId in other rows of this same table. In the example above, row 1 should have deviceModel = HW1, row 2 should have deviceModel = HW2, etc.
谢谢!
推荐答案
首先,这是一个非规范化的设计.您应该将deviceModel-> deviceId关系移动到另一个表.
First of all, this is a denormalised design. You should move deviceModel -> deviceId relation to another table.
第二:
UPDATE
yourTable AS t1
CROSS JOIN (
SELECT DISTINCT
deviceId, deviceModel
FROM
yourTable
WHERE
deviceModel IS NOT NULL
) AS t2
USING (deviceId)
SET
t1.deviceModel = t2.deviceModel
WHERE
t1.deviceModel IS NULL
这篇关于SQL ...如何用同一表中其他行的数据更新行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!