UPDATE语法
更新时间 2025-02-14 10:22:10
最近更新时间: 2025-02-14 10:22:10
本页介绍天翼云TeleDB数据库的UPDATE语法。
单表更新
teledb=# update teledb_serial set nickname = 'random value' where id = 2;
UPDATE 1
teledb=# select * from teledb_serial;
id | nickname
----+---------------
1 | hello teledb
2 | random value
(2 rows)null 条件的表达方法。
teledb=# insert into teledb_serial (id) values(3);
INSERT 0 1
teledb=# select * from teledb_serial;
id | nickname
----+---------------
1 | hello teledb
2 | random value
3 |
(3 rows)
teledb=# update teledb_serial set nickname = 'random value' where nickname is null;;
UPDATE 1
teledb=# select * from teledb_serial;
id | nickname
----+---------------
1 | hello teledb
2 | random value
3 | random value
(3 rows)多表关联更新
teledb=# update teledb_serial set nickname = 'updatefrom' from t_update where t_update.id = teledb_serial.id;
UPDATE 1
teledb=# select * from teledb_serial;
id | nickname
----+--------------
2 | random value
1 | updatefrom
3 | random value
(3 rows)返回更新的数据
teledb=# update teledb_serial set nickname = 'returning' where id = (random()*2)::integer returning *;
id | nickname
----+-----------
1 | returning
(1 row)上面的语句随机更新了一些数据，然后返回更新过的记录，returning 机制旨在降低应用的复杂度。
多列匹配更新
teledb=# alter table t_update add column age int;
ALTER TABLE
teledb=# update t_update set (age , name) = ((random()*2)::integer, 'multi_column');
UPDATE 1
teledb=# select * from t_update ;
id | name | age
----+--------------+-----
1 | multi_column | 0
(1 row)shard key禁止更新操作
teledb=# update t_update set id = 2 where id =1;
ERROR: Distributed column or partition column "id" can't be updated in current version
