4

Vertica has an interesting update syntax when updating a table based on a join value. Instead of using a join to find the update rows, it mandates a syntax like this:

UPDATE a
SET col = b.val
where a.id = b.id

(Note that this syntax is indeed mandated in this case, because Vertica prohibits us from using a where clause that includes a "self-join", that is a join referencing the table being updated, in this case a.)

This syntax is nice, but it's less explicit about the join being used than other SQL dialects. For example, what happens in this case?

UPDATE a
SET col = CASE 0 if b.id IS NULL ELSE b.val END
where a.id = b.id

What happens when a.id has no match in b.id? Does a.col not get updated, as though the condition a.id = b.id represented an inner join of a and b? Or does it get updated to zero, as if the condition were a left outer join?

Paul
  • 3,321
  • 1
  • 33
  • 42

1 Answers1

9

I think Vertica uses the Postgres standard for this syntax:

UPDATE a
    SET col = b.val
    FROM b
    whERE a.id = b.id;

This is an INNER JOIN. I agree that it would be nice if Postgres and the derived databases supported explicit JOINs to the update table (as some other databases do). But the answer to your question is that this is an INNER JOIN.

I should note that if you want a LEFT JOIN, you have two options. One is a correlated subquery:

UPDATE a
    SET col = (SELECT b.val FROM b whERE a.id = b.id);

The other is an additional level of JOIN (assuming that id is unique in a):

UPDATE a
    SET col = b.val
    FROM a a2 LEFT JOIN
         b
         ON a2.id = b.id
    WHERE a.id = a2.id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Do either of these solutions run afoul of the ["no self-joins" rule](https://my.vertica.com/docs/8.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/UPDATE.htm)? I would guess that the first solution is OK but the second is not? – Paul Oct 31 '16 at 20:48
  • I know I could just try this and see what Vertica allows, but I have had bad experiences with Vertica failing silently and producing garbage results on this kind of self-join update query. – Paul Oct 31 '16 at 20:57
  • @Paul . . . Well, the last query clearly violates this rule. I don't know if Vertica permits correlated subqueries in `UPDATE`s. This answer is based on the syntax similarity to Postgres, and all three queries should work in Postgres. – Gordon Linoff Oct 31 '16 at 20:57
  • Would it be more canonical to rely on the default value, so that no update is even needed when there isn't a match? I'm migrating code from MS Access SQL and it's possible the idioms in there don't transfer well. – Paul Oct 31 '16 at 21:27
  • @Paul . . . It would probably be simpler if you could use a default value. – Gordon Linoff Nov 01 '16 at 01:01