UPDATE語法
更新時間 2025-02-14 10:22:26
最近更新時間: 2025-02-14 10:22:26
分享文章
本頁介紹天翼云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
