update table_a a, table_b b set a.username = b.username where a.id = b.id;

-- 以下2种更新结果以table_b的查询结果为准，table_b中没有查到的记录会全部被更新为null,相当于外连接

update table_a a set username = (select name from table_b where id = a.id);

update table_a a left join table_b b on a.id= b.id set a.username = b.name;　

-- insert into 表1(列名) select 列名 from 表2;
insert into table_a(username) select name from table_b where id=1;

-- 1. CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等), 否则, CTE将失效. with 

tab as (

　　select user_code from table_a where user_code like '%ad%'

)

select * from table_b where user_code in (select * from tab)

-- 2. CTE后面也可以跟其他的CTE, 但只能使用一个with, 多个CTE中间用逗号（,）分隔.

with

tab1 as(

　　select * from table_a where name like '赵%'

),

tab2 as(

　　select * from table_b where age> 20

),

tab3 as(

　　select * from table_c where gender = 0

)

select a.* from tab1 a, tab2 b, tab3 c where a.id = b.id and a.id = c.id