lightdb/postgresql多表update更新示例

在ansi sql规范中，是不允许update中包含join的，所以update多表实现通常采用子查询的方式实现，也就是oracle的形式。
在lightdb中，使用update from的形式
UPDATE scholar 
SET STATUS = s.status
FROM student AS s
WHERE scholar.id = s.id;

UPDATE sc_sp_o_c_score
  SET score = tmp.score
  FROM temp_weighted_scores_offers AS tmp
  WHERE tmp.fk_offer = fk_offer
    AND tmp.fk_offer IN (SELECT fk_offer FROM temp_offerids_with_score)
    AND fk_category = 1
    AND fk_searchprofile = 12345;

sql server和pg一样，支持update from。

mysql中的update from支持pg兼容、oracle兼容，以及“UPDATE table1 t1,table2,...,table n”形式来多表更新独有 三种语法。 因为第三种不好理解，所以不推荐。

mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
Query OK, 5 rows affected (0.09 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

oracle update子查询、内联视图或merge

update t1 
   set t1.money = (select t2.money 
                     from t2 
                    where t2.name = t1.name
                   )
where exists (select 1 from t2 where t2.name = t1.name);

-- 不推荐，容易有歧义
update (
        select t1.money money1,t2.money money2 from t1,t2 where t1.name = t2.name
       ) t
   set t.money1 = t.money2;

----
merge into t1
           using (select t2.name,t2.money from t2) t
              on (t.name = t1.name)
    when matched then 
    update  set t1.money = t.money;

　　在内部实现上，update from的流程为，先update xxx from yyy join zzz进行join扁平化，标识xxx为target、xxx.aaa\bbb为targetentry（同时会带回tid便于直接更新），yyy和zzz为source。和merge以及update单表是类似的。