Muti-Column Update, like in Oracle

Moderator: NorbertKrupa

Post Reply
User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Muti-Column Update, like in Oracle

Post by becky » Mon Mar 18, 2013 7:13 pm

Hi guys,

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
But not in Vertica:

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
               ^
Thanks for any assistance!
THANKS - BECKSTER

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Muti-Column Update, like in Oracle

Post by JimKnicely » Mon Mar 18, 2013 9:14 pm

Becky,

Unfortunately for now, you'll have to separate the two column updates using similar sub-queries.

Something like this:

Code: Select all

UPDATE tab1
   SET col2 = (SELECT tab2.col2
                 FROM tab2
                WHERE tab2.col1 = tab1.col1),
       col3 = (SELECT tab2.col3
                 FROM tab2
                WHERE tab2.col1 = tab1.col1);
I hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Re: Muti-Column Update, like in Oracle

Post by becky » Mon Mar 18, 2013 10:49 pm

Wow. Ok. Thanks, Jim. I'm glad I am only updating two columns :shock:
THANKS - BECKSTER

allan
Newbie
Newbie
Posts: 9
Joined: Wed Sep 05, 2012 4:13 pm

Re: Muti-Column Update, like in Oracle

Post by allan » Wed May 15, 2013 8:40 pm

knicely87 wrote:Becky,

Unfortunately for now, you'll have to separate the two column updates using similar sub-queries.
What if the sub-queries are expensive, e.g. from a SELECT statement? Is there any workaround to avoid executing the SELECT for each column?

Thanks,

Allan

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Muti-Column Update, like in Oracle

Post by JimKnicely » Thu May 16, 2013 1:58 pm

Hi,

Perhaps a better solution to this question is to to use the MERGE command. Something like the following:

Code: Select all

MERGE INTO tab1
USING tab2
   ON tab2.c1 = tab1.c1
 WHEN MATCHED THEN UPDATE SET c2 = tab2.c2, c3 = tab2.c3;
Check out the discussion on how we can add some WHERE clause like logic to the MERGE command:

viewtopic.php?f=7&t=153
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

allan
Newbie
Newbie
Posts: 9
Joined: Wed Sep 05, 2012 4:13 pm

Re: Muti-Column Update, like in Oracle

Post by allan » Thu May 16, 2013 8:12 pm

Hi,

thanks for that, unfortunately we're not using 6.x (yet), so I'll do it the long way with temporary tables.

Allan

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Muti-Column Update, like in Oracle

Post by JimKnicely » Thu Jun 12, 2014 12:57 pm

Hi,

I thought I should update this old post! The original question was from Becky:
Can someone help we convert a muti-column UPDATE statement from Oracle to something that'll work in Vertica?
The example below shows the update statement I'd use in Vertica now:

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 t1
dbadmin->    SET col2 = t2.col2,
dbadmin->        col3 = t2.col3
dbadmin->   FROM tab2 t2
dbadmin->  WHERE t2.col1 = t1.col1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM tab1;
 col1 | col2 | col3
------+------+------
    1 | LOW  | LOW
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”