Can someone help we convert a muti-column UPDATE statement from Oracle to something that'll work in Vertica?
The following SQL works fine in Oracle (note the UPDATE statement updates two columns at once):
Code: Select all
SQL> CREATE TABLE tab1 (col1 int, col2 varchar(4), col3 varchar(4));
Table created.
SQL> CREATE TABLE tab2 (col1 int, col2 varchar(4), col3 varchar(4));
Table created.
SQL> INSERT INTO tab1 VALUES (1, 'HIGH', 'HIGH');
1 row created.
SQL> INSERT INTO tab2 VALUES (1, 'LOW', 'LOW');
1 row created.
SQL> SELECT * FROM tab1;
COL1 COL2 COL3
---------- ---- ----
1 HIGH HIGH
SQL> UPDATE tab1
2 SET (tab1.col2, tab1.col3) = (SELECT tab2.col2, tab2.col3
3 FROM tab2
4 WHERE tab2.col1 = tab1.col1);
1 row updated.
SQL> SELECT * FROM tab1;
COL1 COL2 COL3
---------- ---- ----
1 LOW LOW
Code: Select all
dbadmin=> CREATE TABLE tab1 (col1 int, col2 varchar(4), col3 varchar(4));
CREATE TABLE
dbadmin=> CREATE TABLE tab2 (col1 int, col2 varchar(4), col3 varchar(4));
CREATE TABLE
dbadmin=> INSERT INTO tab1 VALUES (1, 'HIGH', 'HIGH');
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO tab2 VALUES (1, 'LOW', 'LOW');
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT * FROM tab1;
col1 | col2 | col3
------+------+------
1 | HIGH | HIGH
(1 row)
dbadmin=> UPDATE tab1
dbadmin-> SET (tab1.col2, tab1.col3) = (SELECT tab2.col2, tab2.col3
dbadmin(> FROM tab2
dbadmin(> WHERE tab2.col1 = tab1.col1);
ERROR 4856: Syntax error at or near "(" at character 20
LINE 2: SET (tab1.col2, tab1.col3) = (SELECT tab2.col2, tab2.col3
^