CODEWITHSUNDEEP
mysqlsql-update
marmeladze
marmeladze

Reputation: 25

mysql table update from other table

i have two tables.

table1 

a -  b  - c-   d  - e
1---b1---c1---d1---e1
2---b2---c2---d2---e2
3---b3---c3---d3---e3
4---b4---c4---d4---e4
5---b5---c5---d5---e5

table2

a----b----c----d----e
1---b2---c2---d2---e2
3---b3---c3---d3---e3
5---b5---c5---d5---e5
6---b6---c6---d6---e6

some information on the table1 is not included in table2 - so i need to update table 2 as a copy of table2. i tried 

UPDATE table1 t1, table2 t2 SET t2.b = t1.b, t2.c = t1.c, t2.d = t1.d

but 0 rows affected - not any change have made. what can i do else?

Upvotes: 0

Views: 172

Answers (2)

user2389633
user2389633

Reputation: 3

If table2 only includes "lines" from table 1, instead of creating another table u could create a view of the 1st table:

 CREATE VIEW view_name AS
   SELECT column_name(s)
   FROM table_name
   WHERE condition

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

First: You need to insert those records that are not presented in the table2 from the table1:

INSERT INTO table2(a, b, c, d, e)
SELECT t1.*
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON  t1.a = t2.a
WHERE t2.a IS NULL;

Then UPDATE them to match table1:

UPDATE table1 t1
INNER JOIN table2 t2 AS t1.a = t2.a 
SET t2.b = t1.b,
       t2.c = t1.c, 
       t2.d = t1.d,
       t2.e = t2.e;

Upvotes: 3

Related Questions