mysql - 如何制作一个较小的表来覆盖另一个较大的表？

Question

我有两张 table

table 1       table 2

id a b c       id a  

1  2 3 a       1  r
2  4 5 b       4  d
3  6 7 c       5  s
4  8 9 d
5  1 2 e
6  2 3 f

我想要 table2 覆盖 table 1。下面是我想要的结果

我想创建一个 View 表

table override

id a b c

1  r 3 a
2  4 5 b
3  6 7 c
4  d 9 d
5  s 2 e
6  2 3 f

我怎样才能做到这一点？谢谢

Answer 1

UPDATE table1 
INNER JOIN table2 ON table1.id=table2.id
SET table1.a=table2.a

if You want view try thiz one :

select t1.id,ifnull(t2.a,t1.a),t1.b from table_1 as t1 left join table_2 as t2 on t2.id=t1.id

创建 View :

创建 View tbl_vw AS SELECT t1.id,COALESCE(t2.a,t1.a),b,c FROM table_1 as t1 left
JOIN table_2 as t2 ON t1.id = t2.id ;

Here is a Link for sample