Postgresql 常用关联增删改SQL

1. 关联新增

INSERT INTO table1(id, name, create_time)
SELECT id, name, now() FROM table2 WHERE name like 'x%';

2. 关联更新

UPDATE table1 tb1
SET name = tb2.name, update_time = now();
FROM table2 tb2
WHERE tb1.id = tb2.id

3. 关联删除

DELETE FROM table1 tb1
USING table2 tb2
WHERE tb1.id = tb2.id AND tb2.del_flg = 1

DELETE FROM table1 tb1
WHERE EXISTS (SELECT 1 FROM table2 WHERE id = tb1.id and del_flg = 1)