CODEWITHSUNDEEP
mysql
S R R
S R R

Reputation: 61

how to update Tables in mysql from another database

I'm trying to UPDATE a table using other database.

I have 2 databases called A_DB and B_DB

In each database I have a table called prvn with these columns and values :

A_DB. prvn : 
id (PK, A_I)   |   dm   |   Name  |    edt  |
1                 1001      David       1
2                 1001      Sam         1
3                 1002      Samin       0

---------------------------------------------

B_DB. prvn : 
id             |   dm   |   Name  |    edt  |
1                 1001      Da          0
2                 1001      Sa          0

I inserted new values from A_DB into B_DB with this code : 

INSERT INTO B_DB.prvn 
SELECT * FROM A_DB.prvn
WHERE A_DB.prvn.id NOT IN (SELECT id FROM B_DB.prvn)

But now I want to Update B_DB values with A_DB values when edt field is change So I use this code : 

UPDATE B_DB.prvn SET 
B_DB.prvn.id = A_DB.prvn.id, B_DB.prvn.dm = A_DB.prvn.dm, B_DB.prvn.Name = A_DB.prvn.Name 
WHERE A_DB.prvn.edt=1 AND A_DB.prvn.edt <> B_DB.prvn.edt

But it doesn't work. Please help how to fix it

Upvotes: 0

Views: 30

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521269

I think you want an update join here:

UPDATE B_DB.prvn b
INNER JOIN A_DB.prvn a
    ON a.id = b.id
SET
    a.prvn.dm = b.prvn.dm,
    a.prvn.Name = b.prvn.Name,
    a.edt = b.edt
WHERE
    a.prvn.edt = 1 AND b.prvn.edt <> 1;

Upvotes: 1

Devratna
Devratna

Reputation: 1008

it will help you to update one table from another.

UPDATE B_DB.prvn a INNER JOIN A_DB.prvn b 
ON a.id = b.id SET a.id = b.id, a.dm=b.dm,
a.Name = b.Name where b.edt=1 and a.edt<>b.edt

Upvotes: 1

Related Questions