如何使用来自同一表中的其他行的数据更新行?

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:

我使用MySQL服务器，我有一个表，其中一些行丢失了数据。我想使用来自其他行的信息更新这些行。我的桌子是这样的:

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

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字段为空。我想使用在同一表的其他行中为deviceModel找到的deviceModel更新这个值。在上面的示例中，第1行应该有deviceModel = HW1，第2行应该有deviceModel = HW2等等。

Thanks!

谢谢!

2 个解决方案

#1

First of all, this is a denormalised design. You should move deviceModel -> deviceId relation to another table.

首先，这是一种非苹果化的设计。您应该将deviceModel -> deviceId关系移动到另一个表。

Second:

第二:

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

#2

Self Join -

自我加入,

UPDATE MyTable m1, MyTable m2 
SET m1.deviceModel= m2.deviceModel
WHERE m1.deviceid=m2.deviceid and m2.deviceModel is not null

#1