SQL ... How to update rows with data from other rows in one table?

Question

I am using a MySQL server and I have a table where some rows are missing data. I would like to update rows using information from other rows. My table looks something like this:

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 deviceId in other rows of the same table. In the above example, line 1 should have deviceModel = HW1, line 2 should have deviceModel = HW2, etc.

Thank!

Answer 1

, . deviceModel → deviceId .

-:

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

Answer 2

Self Join -

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