CODEWITHSUNDEEP
mysqlsql
Yanis Boucherit
Yanis Boucherit

Reputation: 714

Update table from another table and different database

Basically, what I want to do is the following : I have a table 'users' in my first database (prc), like this :

prc.user :
id_user : 45 | name_user : Test | login_user : test | pwd_user : test
[...]

And in my second database (named : prc_test) 

prc_test.user
id_user : 45 | name_user : Test | login_user : test | pwd_user : test
[...]

The thing I want to do, is update all the "pwd_user" fields in "prc_test.user" with the values from pwd_user from "prc.user" But in the prc_test.user, the id are not the same as in prc.user, so I thought of doing it with the "name_user", (there are no doubles).

Any clue in how I can do it ? I searched on Google, but what I found is always for some specific cases, or for insert statements...

(I'm using MySQL5.5)

Thanks !

Upvotes: 30

Views: 33822

Answers (2)

Iulix Dim
Iulix Dim

Reputation: 37

UPDATE prc_test.user SET prc_test.user.pwd_user = (SELECT prc.user.pwd_user FROM prc.user WHERE prc_test.user.id_user= prc.user.id_user)

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

UPDATE 
  prc.user, 
  prc_test.user 
SET 
  prc_test.user.pwd_user = prc.user.pwd_user
WHERE 
  prc_test.user.name_user = prc.user.name_user

Upvotes: 69

Related Questions