github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/update_from (about)

     1  # LogicTest: local fakedist
     2  
     3  statement ok
     4  CREATE TABLE abc (a int primary key, b int, c int)
     5  
     6  statement ok
     7  INSERT INTO abc VALUES (1, 20, 300), (2, 30, 400)
     8  
     9  # Updating using self join.
    10  statement ok
    11  UPDATE abc SET b = other.b + 1, c = other.c + 1 FROM abc AS other WHERE abc.a = other.a
    12  
    13  query III rowsort
    14  SELECT * FROM abc
    15  ----
    16  1  21  301
    17  2  31  401
    18  
    19  # Update only some columns.
    20  statement ok
    21  UPDATE abc SET b = other.b + 1 FROM abc AS other WHERE abc.a = other.a
    22  
    23  query III rowsort
    24  SELECT * FROM abc
    25  ----
    26  1  22  301
    27  2  32  401
    28  
    29  # Update only some rows.
    30  statement ok
    31  UPDATE abc SET b = other.b + 1 FROM abc AS other WHERE abc.a = other.a AND abc.a = 1
    32  
    33  query III rowsort
    34  SELECT * FROM abc
    35  ----
    36  1  23  301
    37  2  32  401
    38  
    39  # Update from another table.
    40  statement ok
    41  CREATE TABLE new_abc (a int, b int, c int)
    42  
    43  statement ok
    44  INSERT INTO new_abc VALUES (1, 2, 3), (2, 3, 4)
    45  
    46  statement ok
    47  UPDATE abc SET b = new_abc.b, c = new_abc.c FROM new_abc WHERE abc.a = new_abc.a
    48  
    49  query III rowsort
    50  SELECT * FROM abc
    51  ----
    52  1  2  3
    53  2  3  4
    54  
    55  # Multiple matching values for a given row. When this happens, we pick
    56  # the first matching value for the row (this is arbitrary). This behavior
    57  # is consistent with Postgres.
    58  statement ok
    59  INSERT INTO new_abc VALUES (1, 1, 1)
    60  
    61  statement ok
    62  UPDATE abc SET b = new_abc.b, c = new_abc.c FROM new_abc WHERE abc.a = new_abc.a
    63  
    64  query III rowsort
    65  SELECT * FROM abc
    66  ----
    67  1  2  3
    68  2  3  4
    69  
    70  # Returning old values.
    71  query IIIII colnames,rowsort
    72  UPDATE abc
    73  SET
    74    b = old.b + 1, c = old.c + 2
    75  FROM
    76    abc AS old
    77  WHERE
    78    abc.a = old.a
    79  RETURNING
    80    abc.a, abc.b AS new_b, old.b as old_b, abc.c as new_c, old.c as old_c
    81  ----
    82  a  new_b  old_b  new_c  old_c
    83  1  3      2      5      3
    84  2  4      3      6      4
    85  
    86  # Check if RETURNING * returns everything
    87  query IIIIII colnames,rowsort
    88  UPDATE abc SET b = old.b + 1, c = old.c + 2 FROM abc AS old WHERE abc.a = old.a RETURNING *
    89  ----
    90  a  b  c  a  b  c
    91  1  4  7  1  3  5
    92  2  5  8  2  4  6
    93  
    94  # Make sure UPDATE FROM works properly in the presence of check columns.
    95  statement ok
    96  CREATE TABLE abc_check (a int primary key, b int, c int, check (a > 0), check (b > 0 AND b < 10))
    97  
    98  statement ok
    99  INSERT INTO abc_check VALUES (1, 2, 3), (2, 3, 4)
   100  
   101  query III colnames,rowsort
   102  UPDATE abc_check
   103  SET
   104    b = other.b, c = other.c
   105  FROM
   106    abc AS other
   107  WHERE
   108    abc_check.a = other.a
   109  RETURNING
   110    abc_check.a, abc_check.b, abc_check.c
   111  ----
   112  a  b  c
   113  1  4  7
   114  2  5  8
   115  
   116  query III rowsort
   117  SELECT * FROM abc
   118  ----
   119  1  4  7
   120  2  5  8
   121  
   122  # Update values of table from values expression
   123  statement ok
   124  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
   125  
   126  query III rowsort
   127  SELECT * FROM abc
   128  ----
   129  1  2  3
   130  2  3  4
   131  
   132  # Check if UPDATE ... FROM works with multiple tables.
   133  statement ok
   134  CREATE TABLE ab (a INT, b INT)
   135  
   136  statement ok
   137  CREATE TABLE ac (a INT, c INT)
   138  
   139  statement ok
   140  INSERT INTO ab VALUES (1, 200), (2, 300)
   141  
   142  statement ok
   143  INSERT INTO ac VALUES (1, 300), (2, 400)
   144  
   145  statement ok
   146  UPDATE abc SET b = ab.b, c = ac.c FROM ab, ac WHERE abc.a = ab.a AND abc.a = ac.a
   147  
   148  query III rowsort
   149  SELECT * FROM abc
   150  ----
   151  1  200  300
   152  2  300  400
   153  
   154  # Make sure UPDATE ... FROM works with LATERAL.
   155  query IIIIIII colnames,rowsort
   156  UPDATE abc
   157  SET
   158    b=ab.b, c = other.c
   159  FROM
   160    ab, LATERAL
   161      (SELECT * FROM ac WHERE ab.a=ac.a) AS other
   162  WHERE
   163    abc.a=ab.a
   164  RETURNING
   165    *
   166  ----
   167  a  b    c    a  b    a  c
   168  1  200  300  1  200  1  300
   169  2  300  400  2  300  2  400
   170  
   171  # Make sure the FROM clause cannot reference the target table.
   172  statement error no data source matches prefix: abc
   173  UPDATE abc SET a = other.a FROM (SELECT abc.a FROM abc AS x) AS other WHERE abc.a=other.a