Firebird 用查询结果集更新数据，merge

Merge语法：

1 MERGE INTO  target [[AS]  target-alias ]
2 USING  source [[AS]  source-alias ]
3 ON  join-condition
4 WHEN MATCHED THEN UPDATE SET  colname =  value [,  colname =  value ...]
5 WHEN NOT MATCHED THEN INSERT [( <columns> )] VALUES ( <values> )

例子A：

1 MERGE INTO books b
2 USING purchases p
3 ON p.title = b.title and p.type = 'bk'
4 WHEN MATCHED THEN
5 UPDATE SET b.desc = b.desc || '; ' || p.desc
6 WHEN NOT MATCHED THEN
7 INSERT (title, desc, bought) values (p.title, p.desc, p.bought)

例子B：

1 MERGE INTO customers c
2 USING (SELECT * from customers_delta WHERE id > 10) cd
3 ON (c.id = cd.id)
4 WHEN MATCHED THEN
5 UPDATE SET name = cd.name
6 WHEN NOT MATCHED THEN
7 INSERT (id, name) values (cd.id, cd.name)

Mysql的语法：注意只能用inner join。

1 Update A inner join B on A.xx = B.xx
2 Set A.xxx = B.xxx