\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
--
-- UPDATE syntax tests
--
CREATE TABLE Update_Test (
a INT DEFAULT 10,
b INT,
c TEXT
);
INSERT INTO Update_Test VALUES (5, 10, 'foo');
INSERT INTO Update_Test(b, a) VALUES (15, 10);
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
----+----+-----
5 | 10 | foo
10 | 15 |
(2 rows)
UPDATE Update_Test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
----+---+-----
10 | |
10 | | foo
(2 rows)
-- aliases for the UPDATE target table
UPDATE Update_Test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
----+----+-----
10 | 10 |
10 | 10 | foo
(2 rows)
UPDATE Update_Test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
----+----+-----
10 | 20 |
10 | 20 | foo
(2 rows)
--
-- Test VALUES in FROM
--
UPDATE Update_Test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE Update_Test.b = v.j;
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
-----+----+-----
100 | 20 |
100 | 20 | foo
(2 rows)
--
-- Test multiple-set-clause syntax
--
UPDATE Update_Test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
-----+----+-------
10 | 31 | bugle
100 | 20 |
(2 rows)
UPDATE Update_Test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM Update_Test ORDER BY a, b, c;
a | b | c
-----+----+-----
11 | 41 | car
100 | 20 |
(2 rows)
-- fail, multi assignment to same column:
UPDATE Update_Test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR: multiple assignments to same column "b"
-- XXX this should work, but doesn't yet:
UPDATE Update_Test SET (a,b) = (select a,b FROM Update_Test where c = 'foo')
WHERE a = 10;
-- if an alias for the target table is specified, don't allow references
-- to the original table name
UPDATE Update_Test AS t SET b = Update_Test.b + 10 WHERE t.a = 10;
ERROR: invalid reference to FROM-clause entry for table "Update_Test"
LINE 1: UPDATE Update_Test AS t SET b = Update_Test.b + 10 WHERE t.a...
^
HINT: Perhaps you meant to reference the table alias "t".
CONTEXT: referenced column: b
-- Make sure that we can update to a TOASTed value.
UPDATE Update_Test SET c = repeat('x', 10000) WHERE c = 'car';
SELECT a, b, char_length(c) FROM Update_Test ORDER BY a;
a | b | char_length
-----+----+-------------
11 | 41 | 10000
100 | 20 |
(2 rows)
DROP TABLE Update_Test;
--Test "update tablename AS aliasname SET aliasname.colname = colvalue;"
CREATE TABLE Update_Test_c(
a INT DEFAULT 10
);
CREATE TABLE Update_Test_d(
a INT DEFAULT 10,
b INT
);
INSERT INTO Update_Test_c (a) VALUES (1);
SELECT * FROM Update_Test_c;
a
---
1
(1 row)
UPDATE Update_Test_c AS test_c SET test_c.a = 2;
SELECT * FROM Update_Test_c;
a
---
2
(1 row)
UPDATE Update_Test_c AS test_c SET test_c.a = 3 WHERE test_c.a = 2;
SELECT * FROM Update_Test_c;
a
---
3
(1 row)
UPDATE Update_Test_c test_c SET test_c.a = 4;
SELECT * FROM Update_Test_c;
a
---
4
(1 row)
UPDATE Update_Test_c AS test_c SET test_c.a = 5 WHERE test_c.a = 4;
SELECT * FROM Update_Test_c;
a
---
5
(1 row)
UPDATE Update_Test_c AS test_c SET test_a.a = 6;
ERROR: column "test_a.a" of relation "Update_Test_c" does not exist
LINE 1: UPDATE Update_Test_c AS test_c SET test_a.a = 6;
^
SELECT * FROM Update_Test_c;
a
---
5
(1 row)
UPDATE Update_Test_c test_c SET test_a.a = 7;
ERROR: column "test_a.a" of relation "Update_Test_c" does not exist
LINE 1: UPDATE Update_Test_c test_c SET test_a.a = 7;
^
SELECT * FROM Update_Test_c;
a
---
5
(1 row)
INSERT INTO Update_Test_d (a,b) VALUES (1,2);
SELECT * FROM Update_Test_d;
a | b
---+---
1 | 2
(1 row)
UPDATE Update_Test_d AS test_D SET test_D.a = 3, test_D.b = 4;
SELECT * FROM Update_Test_d;
a | b
---+---
3 | 4
(1 row)
UPDATE Update_Test_d AS test_D SET test_D.a = 5, test_D.b = 6 WHERE test_D.a = 3 AND test_D.b = 4;
SELECT * FROM Update_Test_d;
a | b
---+---
5 | 6
(1 row)
UPDATE Update_Test_d test_D SET test_D.a = 7, test_D.b = 8;
SELECT * FROM Update_Test_d;
a | b
---+---
7 | 8
(1 row)
UPDATE Update_Test_d test_D SET test_D.a = 9, test_D.b = 10 WHERE test_D.a = 7 AND test_D.b = 8;
SELECT * FROM Update_Test_d;
a | b
---+----
9 | 10
(1 row)
UPDATE Update_Test_d AS test_D SET test_D.a = 11, test_b.b = 12;
ERROR: column "test_b.b" of relation "Update_Test_d" does not exist
LINE 1: UPDATE Update_Test_d AS test_D SET test_D.a = 11, test_b.b =...
^
SELECT * FROM Update_Test_d;
a | b
---+----
9 | 10
(1 row)
UPDATE Update_Test_d test_D SET test_D.a = 11, test_b.b = 12;
ERROR: column "test_b.b" of relation "Update_Test_d" does not exist
LINE 1: UPDATE Update_Test_d test_D SET test_D.a = 11, test_b.b = 12...
^
SELECT * FROM Update_Test_d;
a | b
---+----
9 | 10
(1 row)
DROP TABLE Update_Test_c;
DROP TABLE Update_Test_d;
DROP TABLE Update_Test_d;
ERROR: table "Update_Test_d" does not exist
create table tbl_Update(a1 int,a2 varchar2(100));
ALTER TABLE tbl_Update ADD PRIMARY KEY(a1);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tbl_Update_pkey" for table "tbl_Update"
delete from tbl_Update;
insert into tbl_Update values(1,'a');
insert into tbl_Update values(2,'b');
insert into tbl_Update values(3,'c');
insert into tbl_Update values(4,'d');
insert into tbl_Update values(11,'AA');
select * from tbl_Update order by a1;
a1 | a2
----+----
1 | a
2 | b
3 | c
4 | d
11 | AA
(5 rows)
create table sub_tab(T1 int,t2 varchar2(100));
insert into sub_tab values(11,'AA');
select * from sub_tab;
T1 | t2
----+----
11 | AA
(1 row)
update tbl_Update a set (a1,a2)=(100,'hello') from sub_tab t where t.T1=a.a1;
select * from tbl_Update order by a1;
a1 | a2
-----+-------
1 | a
2 | b
3 | c
4 | d
100 | hello
(5 rows)
update tbl_Update a1 set (a1,a2)=(101,'hello world') from sub_tab t where t.T1=a1.a1;
select * from tbl_Update order by a1;
a1 | a2
-----+-------
1 | a
2 | b
3 | c
4 | d
100 | hello
(5 rows)
drop table tbl_Update;
drop table sub_tab;
create table test_tbl_A(a int);
insert into test_tbl_A values(1);
select * from test_tbl_A;
a
---
1
(1 row)
update test_tbl_A a set a=2;
select * from test_tbl_A;
a
---
2
(1 row)
update test_tbl_A a set a=3 where a.a=2;
select * from test_tbl_A;
a
---
3
(1 row)
drop table test_tbl_A;
create table test_tbl_B(a int, b int);
insert into test_tbl_B values(1,2);
select * from test_tbl_B;
a | b
---+---
1 | 2
(1 row)
update test_tbl_B as a set (a,b)=(3,4);
update test_tbl_B set c = 100;
ERROR: column "c" of relation "test_tbl_B" does not exist
LINE 1: update test_tbl_B set c = 100;
^
select * from test_tbl_B;
a | b
---+---
3 | 4
(1 row)
update test_tbl_B as a set (a,b)=(5,6) where a.a=3 and a.b=4;
select * from test_tbl_B;
a | b
---+---
5 | 6
(1 row)
update test_tbl_B as a set (a.a, a.b)=(7,8) where a.a=5 and a.b=6;
select * from test_tbl_B;
a | b
---+---
7 | 8
(1 row)
drop table test_tbl_B;
CREATE TYPE complex AS (b int,c int);
CREATE TYPE complex AS (b int,c int);
ERROR: type "complex" already exists
create table test_tbl_C(a complex);
ALTER TABLE test_tbl_C ADD PRIMARY KEY(a);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_tbl_C_pkey" for table "test_tbl_C"
insert into test_tbl_C values((1,2));
select * from test_tbl_C;
a
-------
(1,2)
(1 row)
update test_tbl_C col set col.a.b=(100);
select * from test_tbl_C;
a
---------
(100,2)
(1 row)
drop table test_tbl_C;
drop type complex;
-- Test multiple column set with GROUP BY of UPDATE
CREATE TABLE Update_Multiple_Set_01(a INT, b INT, c INT);
CREATE TABLE Update_Multiple_Set_02(a INT, b INT, c INT);
UPDATE Update_Multiple_Set_02 t2 SET (b, c) = (SELECT b, c FROM Update_Multiple_Set_01 T1 WHERE T1.a=t2.a GROUP BY 1, 2);
DROP TABLE Update_Multiple_Set_01;
DROP TABLE Update_Multiple_Set_02;
-- Test multiple column set with GROUP BY alias of UPDATE
drop table Usview08t;
ERROR: table "Usview08t" does not exist
drop table Offers_20050701;
ERROR: table "Offers_20050701" does not exist
create table Usview08t(location_id int, on_hand_unit_qty int, on_order_qty int);
create table Offers_20050701(location_id int null, visits int null);
insert into Usview08t values(1,3,5);
insert into Offers_20050701 values(2,4);
UPDATE Usview08t Table_008 SET (on_hand_unit_qty,on_order_qty) = (SELECT AVG(VISITS),154 c2 FROM Offers_20050701 GROUP BY c2);
select * from Usview08t;
location_id | on_hand_unit_qty | on_order_qty
-------------+------------------+--------------
1 | 4 | 154
(1 row)
UPDATE Usview08t t2 SET (t2.on_hand_unit_qty, t2.on_order_qty) = (SELECT AVG(VISITS),154 FROM Offers_20050701);
UPDATE Usview08t Table_008 SET (on_hand_unit_qty,on_hand_unit_qty) = (SELECT AVG(VISITS),154 c2 FROM Offers_20050701 GROUP BY c2);
ERROR: multiple assignments to same column "on_hand_unit_qty"
drop table Usview08t;
drop table Offers_20050701;
--Test table name reference or alias reference
create table Test (b int, a int);
insert into Test values(1,2);
update Test set Test.a=10;
update Test t set t.b=20;
select * from Test;
b | a
----+----
20 | 10
(1 row)
drop table Test;
create table Test(a int[3],b int);
insert into Test values('{1,2,3}',4);
update Test set Test.a='{10,20,30}';
select * from Test;
a | b
------------+---
{10,20,30} | 4
(1 row)
update Test t set t.a='{11,21,31}';
select * from Test;
a | b
------------+---
{11,21,31} | 4
(1 row)
update Test set a='{12,22,32}';
select * from Test;
a | b
------------+---
{12,22,32} | 4
(1 row)
update Test set a[1,2]='{13,23}';
select * from Test;
a | b
------------+---
{13,23,32} | 4
(1 row)
--must compatible with previous features, though not perfect
update Test set Test.a[1,2]='{14,24}';
select * from Test;
a | b
------------+---
{14,24,32} | 4
(1 row)
update Test t set t.a[1,2]='{15,25}';
select * from Test;
a | b
------------+---
{15,25,32} | 4
(1 row)
drop table Test;
create type newtype as(a int, b int);
create table Test(a newtype,b int);
insert into Test values(ROW(1,2),3);
update Test set Test.a=ROW(10,20);
select * from Test;
a | b
---------+---
(10,20) | 3
(1 row)
update Test t set t.a=ROW(11,21);
select * from Test;
a | b
---------+---
(11,21) | 3
(1 row)
--Ambiguous scene
--update field a of column a rather than column a of table a
update Test a set a.a=12;
NOTICE: update field 'a' of column 'a', though it's ambiguous.
--update field b of column a rather than column b of table a
update Test a set a.b=22;
NOTICE: update field 'b' of column 'a', though it's ambiguous.
select * from Test;
a | b
---------+---
(12,22) | 3
(1 row)
--fail
update Test a set a.a=ROW(13,23);
NOTICE: update field 'a' of column 'a', though it's ambiguous.
ERROR: subfield "a" is of type integer but expression is of type record
LINE 1: update Test a set a.a=ROW(13,23);
^
HINT: You will need to rewrite or cast the expression.
CONTEXT: referenced column: a
update Test a set a.c=10;
NOTICE: update field 'c' of column 'a', though it's ambiguous.
ERROR: cannot assign to field "c" of column "a" because there is no such column in data type newtype
LINE 1: update Test a set a.c=10;
^
CONTEXT: referenced column: a
update Test b set b.c=10;
ERROR: column "c" of relation "Test" does not exist
LINE 1: update Test b set b.c=10;
^
--must compatible with previous features, though not perfect
update Test a set a.a.a=12;
select * from Test;
a | b
---------+---
(12,22) | 3
(1 row)
drop table Test;
drop type newtype;
--Test update in merge into
create table test_D (a int, b int);
create table test_S (a int, b int);
insert into test_D values(generate_series(6,10),1);
insert into test_S values(generate_series(1,10),2);
merge into test_D using test_S on(test_D.a=test_S.a) when matched then update set test_D.b=test_S.b;
select * from test_D order by a;
a | b
----+---
6 | 2
7 | 2
8 | 2
9 | 2
10 | 2
(5 rows)
truncate table test_S;
insert into test_S values(generate_series(1,10),20);
merge into test_D d using test_S on(d.a=test_S.a) when matched then update set d.b=test_S.b;
select * from test_D order by a;
a | b
----+----
6 | 20
7 | 20
8 | 20
9 | 20
10 | 20
(5 rows)
drop table test_D;
drop table test_S;
create table test_D(a int[3],b int);
create table test_S(a int[3],b int);
insert into test_D values('{1,2,3}',4);
insert into test_S values('{10,20,30}',4);
merge into test_D using test_S on(test_D.b=test_S.b) when matched then update set test_D.a=test_S.a;
select * from test_D;
a | b
------------+---
{10,20,30} | 4
(1 row)
truncate table test_S;
insert into test_S values('{11,21,31}',4);
merge into test_D d using test_S on(d.b=test_S.b) when matched then update set d.a=test_S.a;
select * from test_D;
a | b
------------+---
{11,21,31} | 4
(1 row)
--must compatible with previous features, though not perfect
merge into test_D using test_S on(test_D.b=test_S.b) when matched then update set test_D.a[1,3]=test_S.a[1,3];
select * from test_D;
a | b
------------+---
{11,21,31} | 4
(1 row)
merge into test_D d using test_S on(d.b=test_S.b) when matched then update set d.a[1,3]=test_S.a[1,3];
select * from test_D;
a | b
------------+---
{11,21,31} | 4
(1 row)
drop table test_D;
drop table test_S;
create type newtype as(a int,b int);
create table test_D(a newtype, b int);
create table test_S(a newtype, b int);
insert into test_D values(ROW(1,2),3);
insert into test_S values(ROW(10,20),3);
merge into test_D using test_S on(test_D.b=test_S.b) when matched then update set test_D.a=test_S.a;
select * from test_D;
a | b
---------+---
(10,20) | 3
(1 row)
truncate table test_S;
insert into test_S values(ROW(11,12),3);
merge into test_D d using test_S on(d.b=test_S.b) when matched then update set d.a=test_S.a;
select * from test_D;
a | b
---------+---
(11,12) | 3
(1 row)
truncate table test_S;
insert into test_S values(ROW(22,22),3);
merge into test_D a using test_S on(a.b=test_S.b) when matched then update set a.a=21;
NOTICE: update field 'a' of column 'a', though it's ambiguous.
merge into test_D a using test_S on(a.b=test_S.b) when matched then update set a.b=22;
NOTICE: update field 'b' of column 'a', though it's ambiguous.
select * from test_D;
a | b
---------+---
(21,22) | 3
(1 row)
--fail
merge into test_D a using test_S on(a.b=test_S.b) when matched then update set a.a=test_S.a;
NOTICE: update field 'a' of column 'a', though it's ambiguous.
ERROR: subfield "a" is of type integer but expression is of type newtype
LINE 1: ...t_S on(a.b=test_S.b) when matched then update set a.a=test_S...
^
HINT: You will need to rewrite or cast the expression.
CONTEXT: referenced column: a
--must compatible with previous features, though not perfect
merge into test_D using test_S on(test_D.b=test_S.b) when matched then update set test_D.a.a=test_S.b;
select * from test_D;
a | b
--------+---
(3,22) | 3
(1 row)
merge into test_D d using test_S on(d.b=test_S.b) when matched then update set d.a.a=test_S.b;
select * from test_D;
a | b
--------+---
(3,22) | 3
(1 row)
drop table test_S;
drop table test_D;
drop type newtype;
-- Test update multiple entries for the same column with subselect
create table Test (a int[2], b int);
insert into Test values('{1,2}',3);
update Test set (a[1],a[2])=(select 10,20);
select * from Test;
a | b
---------+---
{10,20} | 3
(1 row)
drop table Test;
create type nt as(a int,b int);
create table Test(a nt,b nt,c int);
insert into Test values(row(1,2),row(3,4),5);
update Test set (a.b,b.b)=(select 20,40);
select * from Test;
a | b | c
--------+--------+---
(1,20) | (3,40) | 5
(1 row)
drop table Test;
drop type nt;
-- Test comment in subselect of update
create table Test(a int,b int);
insert into Test values(1,2);
update Test set (a)=(select /*comment*/10);
select * from Test;
a | b
----+---
10 | 2
(1 row)
update Test set (a)=(select /*+comment*/20);
select * from Test;
a | b
----+---
20 | 2
(1 row)
drop table Test;
--Test update multiple fields of column which using composite type at once
create type nt as(a int,b int);
create table AA (a nt, b int,c char);
explain (verbose on, costs off) insert into AA values(ROW(1,2),3,'4');
QUERY PLAN
-------------------------------------------------
Insert on public."AA"
-> Result
Output: ROW(1, 2), 3, '4'::character(1)
(3 rows)
insert into AA values(ROW(1,2),3,'4');
explain (verbose on, costs off) update AA set a.a=10,a.b=20 where c='4';
QUERY PLAN
-----------------------------------------
Update on public."AA"
-> Seq Scan on public."AA"
Output: ROW(10, 20), b, c, ctid
Filter: ("AA".c = '4'::bpchar)
(4 rows)
update AA set a.a=10,a.b=20 where c='4';
select * from AA;
a | b | c
---------+---+---
(10,20) | 3 | 4
(1 row)
drop table AA;
drop type nt;
--Test update multiple values of of an array at once
create table Test (a int[2], b int,c char);
insert into Test values('{1,2}',3,'4');
explain (verbose on, costs off) update Test set a[1]=100,a[2]=200 where c='4';
QUERY PLAN
-----------------------------------------------------
Update on public."Test"
-> Seq Scan on public."Test"
Output: (a[1] := 100)[2] := 200, b, c, ctid
Filter: ("Test".c = '4'::bpchar)
(4 rows)
update Test set a[1]=100,a[2]=200 where c='4';
select * from Test;
a | b | c
-----------+---+---
{100,200} | 3 | 4
(1 row)
explain (verbose on, costs off) update Test set a[1,2]='{101,201}' where c='4';
QUERY PLAN
--------------------------------------------------------------
Update on public."Test"
-> Seq Scan on public."Test"
Output: a[1:2] := '{101,201}'::integer[], b, c, ctid
Filter: ("Test".c = '4'::bpchar)
(4 rows)
update Test set a[1,2]='{101,201}' where c='4';
select * from Test;
a | b | c
-----------+---+---
{101,201} | 3 | 4
(1 row)
explain (verbose on, costs off) insert into Test (a[1,2],b,c) values('{113,114}',4,'5');
QUERY PLAN
----------------------------------------------------------------------------------------
Insert on public."Test"
-> Result
Output: (NULL::integer[])[1:2] := '{113,114}'::integer[], 4, '5'::character(1)
(3 rows)
insert into Test (a[1,2],b,c) values('{113,114}',4,'5');
select * from Test order by 3;
a | b | c
-----------+---+---
{101,201} | 3 | 4
{113,114} | 4 | 5
(2 rows)
select a[1,2] from Test where c='4';
a
-----------
{101,201}
(1 row)
explain (verbose on, costs off) insert into Test (a[1],a[2],b,c)values(1,2,3,'6');
QUERY PLAN
---------------------------------------------------------------------------
Insert on public."Test"
-> Result
Output: ((NULL::integer[])[1] := 1)[2] := 2, 3, '6'::character(1)
(3 rows)
insert into Test (a[1],a[2],b,c)values(1,2,3,'6');
select * from Test order by 3;
a | b | c
-----------+---+---
{101,201} | 3 | 4
{113,114} | 4 | 5
{1,2} | 3 | 6
(3 rows)
explain (verbose on, costs off) insert into Test (a[1:2],b,c)values('{1,2}',3,'7');
QUERY PLAN
------------------------------------------------------------------------------------
Insert on public."Test"
-> Result
Output: (NULL::integer[])[1:2] := '{1,2}'::integer[], 3, '7'::character(1)
(3 rows)
insert into Test (a[1:2],b,c)values('{1,2}',3,'7');
select * from Test order by 3;
a | b | c
-----------+---+---
{101,201} | 3 | 4
{113,114} | 4 | 5
{1,2} | 3 | 6
{1,2} | 3 | 7
(4 rows)
explain (verbose on, costs off) update Test set a[1:2]='{10,20}' where c='7';
QUERY PLAN
------------------------------------------------------------
Update on public."Test"
-> Seq Scan on public."Test"
Output: a[1:2] := '{10,20}'::integer[], b, c, ctid
Filter: ("Test".c = '7'::bpchar)
(4 rows)
update Test set a[1:2]='{10,20}' where c='7';
select * from Test order by 3;
a | b | c
-----------+---+---
{101,201} | 3 | 4
{113,114} | 4 | 5
{1,2} | 3 | 6
{10,20} | 3 | 7
(4 rows)
drop table Test;
