update_from

# LogicTest: local-opt fakedist-opt

statement ok
CREATE TABLE abc (a int primary key, b int, c int)

statement ok
INSERT INTO abc VALUES (1, 20, 300), (2, 30, 400)

# Updating using self join.
statement ok
UPDATE abc SET b = other.b + 1, c = other.c + 1 FROM abc AS other WHERE abc.a = other.a

query III rowsort
SELECT * FROM abc
----
1  21  301
2  31  401

# Update only some columns.
statement ok
UPDATE abc SET b = other.b + 1 FROM abc AS other WHERE abc.a = other.a

query III rowsort
SELECT * FROM abc
----
1  22  301
2  32  401

# Update only some rows.
statement ok
UPDATE abc SET b = other.b + 1 FROM abc AS other WHERE abc.a = other.a AND abc.a = 1

query III rowsort
SELECT * FROM abc
----
1  23  301
2  32  401

# Update from another table.
statement ok
CREATE TABLE new_abc (a int, b int, c int)

statement ok
INSERT INTO new_abc VALUES (1, 2, 3), (2, 3, 4)

statement ok
UPDATE abc SET b = new_abc.b, c = new_abc.c FROM new_abc WHERE abc.a = new_abc.a

query III rowsort
SELECT * FROM abc
----
1  2  3
2  3  4

# Multiple matching values for a given row. When this happens, we pick
# the first matching value for the row (this is arbitrary). This behavior
# is consistent with Postgres.
statement ok
INSERT INTO new_abc VALUES (1, 1, 1)

statement ok
UPDATE abc SET b = new_abc.b, c = new_abc.c FROM new_abc WHERE abc.a = new_abc.a

query III rowsort
SELECT * FROM abc
----
1  2  3
2  3  4

# Returning old values.
query IIIII colnames
UPDATE abc
SET
  b = old.b + 1, c = old.c + 2
FROM
  abc AS old
WHERE
  abc.a = old.a
RETURNING
  abc.a, abc.b AS new_b, old.b as old_b, abc.c as new_c, old.c as old_c
----
a  new_b  old_b  new_c  old_c
1  3      2      5      3
2  4      3      6      4

# Check if RETURNING * returns everything
query IIIIII colnames rowsort
UPDATE abc SET b = old.b + 1, c = old.c + 2 FROM abc AS old WHERE abc.a = old.a RETURNING *
----
a  b  c  a  b  c
1  4  7  1  3  5
2  5  8  2  4  6

# Make sure UPDATE FROM works properly in the presence of check columns.
statement ok
CREATE TABLE abc_check (a int primary key, b int, c int, check (a > 0), check (b > 0 AND b < 10))

statement ok
INSERT INTO abc_check VALUES (1, 2, 3), (2, 3, 4)

query III colnames
UPDATE abc_check
SET
  b = other.b, c = other.c
FROM
  abc AS other
WHERE
  abc_check.a = other.a
RETURNING
  abc_check.a, abc_check.b, abc_check.c
----
a  b  c
1  4  7
2  5  8

# Update values of table from values expression
statement ok
UPDATE abc SET b = other.b, c = other.c FROM (values (1, 2, 3), (2, 3, 4)) as other ("a", "b", "c") WHERE abc.a = other.a

query III rowsort
SELECT * FROM abc
----
1  2  3
2  3  4

# Check if UPDATE ... FROM works with multiple tables.
statement ok
CREATE TABLE ab (a INT, b INT)

statement ok
CREATE TABLE ac (a INT, c INT)

statement ok
INSERT INTO ab VALUES (1, 200), (2, 300)

statement ok
INSERT INTO ac VALUES (1, 300), (2, 400)

statement ok
UPDATE abc SET b = ab.b, c = ac.c FROM ab, ac WHERE abc.a = ab.a AND abc.a = ac.a

query III rowsort
SELECT * FROM abc
----
1  200  300
2  300  400

# Make sure UPDATE ... FROM works with LATERAL.
query IIIIIII colnames rowsort
UPDATE abc
SET
  b=ab.b, c = other.c
FROM
  ab, LATERAL
    (SELECT * FROM ac WHERE ab.a=ac.a) AS other
WHERE
  abc.a=ab.a
RETURNING
  *
----
a  b    c    a  b    a  c
1  200  300  1  200  1  300
2  300  400  2  300  2  400


# Make sure the FROM clause cannot reference the target table.
statement error no data source matches prefix: abc
UPDATE abc SET a = other.a FROM (SELECT abc.a from abc as x) as other WHERE abc.a=other.a