CODEWITHSUNDEEP
mysql
Gabriel Alain
Gabriel Alain

Reputation: 1

Update value in a table with value from another table in mysql

I have tbltransfer which contains location and eq_id. I would like to change the value of location in tblequipments with the value of location from tbltransfer where eq_id in tblequipments is equal to the eq_id in tbltransfer. This the query that I came up with:

UPDATE tblequipments 
    SET LocationName = (
        SELECT t.Location from tbltransfer as t 
        join tblequipments as e on t.eq_ID = e.eq_ID 
        WHERE t.transactionID=%s
    )

Upvotes: 0

Views: 51

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65332

UPDATE tblequipments
INNER JOIN tbltransfer ON tblequipments.eq_ID = tbltransfer.eq_ID 
SET tblequipments.LocationName=tbltransfer.Location
WHERE tbltransfer.transactionID=%s

Upvotes: 1

Vitaly Muminov
Vitaly Muminov

Reputation: 1952

mysql will run subquery for each row in update statement. It is better to use join here:

UPDATE
    tblequipments,
    tbltransfer
SET
    tblequipments.location = tbltransfer.location
WHERE
    tblequipments.eq_id = tbltransfer.eq_id
AND
    tbltransfer.transactionID = %s

Upvotes: 1

Related Questions