Options

Update - Outer Join

How I update the table by outer joining with another tables? 

As you see in the following UPDATE, it works well in Vertica. But it performs an inner join between customers, t1, t2, t3 table/subqueries.

UPDATE customers
SET 
col1 = t1.val,
col2 = t2.val,
col3 = t3.val
FROM
(
select * from cust_t1
) as t1,
(
select * from cust_t2
) as t2,
(
select * from cust_t3
) as t3
WHERE
customers.uid = t1.uid AND
customers.uid = t2.uid AND
customers.uid = t3.uid;
How can I point the outer join? If I use Oracle I can write this as following:

UPDATE customers
SET 
col1 = t1.val,
col2 = t2.val,
col3 = t3.val
FROM
(
select * from cust_t1
) as t1,
(
select * from cust_t2
) as t2,
(
select * from cust_t3
) as t3
WHERE
customers.uid = t1.uid(+) AND
customers.uid = t2.uid(+) AND
customers.uid = t3.uid(+);
I could not find any related example and documentation does not point the outer join in the update clauses.

Regards.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file