CODEWITHSUNDEEP
sqlvertica
Biswabid
Biswabid

Reputation: 1411

How to do inner join in Vertica Update?

I was trying to do a inner join with 3 tables in a update query. I tried to find the solution in multiple sites but didn't get the solution.
Following a sample query I am trying:

UPDATE TGT
SET C1 = CASE WHEN TGT.c2 = SRC.c2 AND SRC.C3 = 'P' THEN SRC.C1 ELSE NULL END,
C2 = CASE WHEN TGT.c2 = SRC.c2 AND SRC.C3 = 'D' THEN SRC.C1 ELSE NULL END
FROM SRC
INNER JOIN SRC1
ON SRC.C9 = SRC1.C9
AND SRC.C9 = TGT.C9;

Thanks in Advance!!

Upvotes: 2

Views: 1346

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would expect your syntax to work. (I don't have Vertica handy but its query parser is based on Postgres.)

Perhaps -- unlike Postgres -- JOIN is not allowed in the FROM. Then you can put the join conditions in the WHERE clause:

UPDATE TGT
    SET C1 = (CASE WHEN TGT.c2 = SRC.c2 AND SRC.C3 = 'P' THEN SRC.C1 END)
        C2 = (CASE WHEN TGT.c2 = SRC.c2 AND SRC.C3 = 'D' THEN SRC.C1 END)
FROM SRC, SRC1
WHERE SRC.C9 = SRC1.C9 AND SRC.C9 = TGT.C9;

Upvotes: 1

Related Questions