Update one database with info from another

Question

I have this query

SELECT pu.payroleId, u.userId, u.firstName, u.lastName
FROM users AS u, payrole_users AS pu
WHERE pu.firstName = u.firstName
AND pu.lastName = u.lastName
LIMIT 0 , 60

Which selects all userId's from the users table if the names matches the names in the payrole_users table (if I've got the query right!). What I need is: for each userId, I need to update the payrole_users.intranetId field. Is this possible to do with just one query or would I need to store all these as an array and then loop through the array after and then update it?

score 1 · Accepted Answer · answered Mar 12 '14 at 17:09

1

Try

UPDATE users u, payrole_users pu
SET pu.intranetId=u.userId
WHERE pu.firstName = u.firstName AND pu.lastName = u.lastName;

answered Mar 12 '14 at 17:09

Miguel Delgado

443
3
9

score 0 · Answer 2 · edited May 23 '17 at 12:27

Try testing this query out - does it do what you want?

UPDATE 
    payrole_users AS pu
        JOIN
    users AS u ON pu.firstName = u.firstname AND pu.lastName = u.lastName
SET
    pu.intranetId = u.userId

Note that this:

FROM tableA AS A JOIN tableB AS B ON A.id = B.id

is effectively the same as this:

FROM tableA AS A, tableB AS B WHERE A.id = B.id

For more information, see Explicit vs implicit SQL joins.

Update one database with info from another

2 Answers2