CODEWITHSUNDEEP
mysqlsql
ilhan
ilhan

Reputation: 8995

Multiple Updates with table comparison in MySQL

I couldn't figure out how to make multiple updates with one single query.

Here is my 2 tables.

devices_table

DeviceID  Brand     SerialNumber
---------------------------------
1         Nintendo  324234324
2         Nintendo  89978333
3         Sony      Z3432343
4         Sony      Z3424335

temporary_table

DeviceID  Brand     SerialNumber
---------------------------------
NULL      Nintendo  324234324
NULL      Nintendo  89978333
NULL      Sony      Z3432343
NULL      Sony      Z3424335

How I'll fill the DeviceID in temporary_table? What kind of query I do need?

Upvotes: 0

Views: 43

Answers (2)

GarethD
GarethD

Reputation: 69789

You need to use an update statement:

UPDATE  temporary_table
SET     DeviceID = (    SELECT  Devices_table.DeviceID
                        FROM    Devices_table
                        WHERE   Devices_table.Brand = temporary_table.Brand
                        AND     Devices_table.SerialNumber = temporary_table.SerialNumber
                    );

Example on SQL Fiddle

Or you can use a join rather than a correlated subquery:

UPDATE  temporary_table
        INNER JOIN Devices_table
            ON Devices_table.Brand = temporary_table.Brand
            AND Devices_table.SerialNumber = temporary_table.SerialNumber
SET     temporary_table.DeviceID = Devices_table.DeviceID;

Example on SQL Fiddle

Upvotes: 2

user2001117
user2001117

Reputation: 3777

Yes u can use the below query:

UPDATE  temporary_table
SET     DeviceID = Devices_table.DeviceID
FROM    temporary_table
INNER JOIN Devices_table
ON Devices_table.Brand = temporary_table.Brand
AND Devices_table.SerialNumber = temporary_table.SerialNumber;

Upvotes: 0

Related Questions