一、单表更新
null 条件的表达方法。
postgres=# update tdsql_pg set nickname = 'Good tdsql_pg' where nickname is null;UPDATE 1postgres=# select * from tdsql_pg; id | nickname —-+————- 2 | tdsql_pg好 1 | Hello tdsql_pg 3 | Good tdsql_pg(3 rows)
二、多表关联更新
postgres=# update tdsql_pg set nickname ='Good tdsql_pg' from t_appoint_col where t_appoint_col.id=tdsql_pg.id;UPDATE 1postgres=# select * from tdsql_pg; id | nickname —-+———— 2 | tdsql_pg好 1 | Good tdsql_pg(2 rows)
三、返回更新的数据
postgres=# update tdsql_pg set nickname = nickname where id = (random()*2)::integer returning *; id | nickname —-+———– 2 | tdsql_pg好(1 row)
上面的语句随机更新了一些数据，然后返回更新过的记录，returning 机制旨在降低应用的复杂度。
四、多列匹配更新
postgres=# update tdsql_pg set ( nickname , age ) = ( 'tdsql_pg好' , (random()*2)::integer );UPDATE 2postgres=# select * from tdsql_pg; id | nickname | age —-+———–+—– 1 | tdsql_pg好 | 2 2 | tdsql_pg好 | 0(2 rows)
五、shard key 禁止更新操作
postgres=# update tdsql_pg set id=8 where id=1; ERROR: Distribute column or partition column can't be updated in current version