CODEWITHSUNDEEP
vertica
Arpit Srivastava
Arpit Srivastava

Reputation: 121

How to Update a table in vertica with a fixed value while joining with other table?

I have This query in Mysql:

UPDATE table1 AS a
JOIN table2  AS b ON a.code=b.code
AND b.rating < 3
SET a.Status = 'Delisted';

When using this in Vertica I am getting the error: 'Syntax error at or near "JOIN"' Can someone help please. Thanks in advance

Upvotes: 3

Views: 3620

Answers (2)

user4512135
user4512135

Reputation:

Try this(without JOIN, Vertica is smart enough):

update table1 as a set status = 'delisted'
  from table2 as b
  where a.code = b.code and b.rating < 3;

For more info read here

Upvotes: 5

Up_One
Up_One

Reputation: 5271

In Vertica the syntax is a bit different, you will have to use the EXISTS statement

UPDATE table1
   SET Status = 'Delisted'
 WHERE EXISTS (select *
          from table1 a
          JOIN table2 b ON a.code = b.code
                       AND b.rating < 3);

On JOINS allowed on updates. I hope this was helpfull.

Upvotes: 0

Related Questions