CODEWITHSUNDEEP
mysql
TMH
TMH

Reputation: 6246

Update one database with info from another

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?

Upvotes: 0

Views: 21

Answers (2)

Air
Air

Reputation: 8595

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.

Upvotes: 0

Miguel Delgado
Miguel Delgado

Reputation: 443

Try

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

Upvotes: 1

Related Questions