CODEWITHSUNDEEP
sqljoinsql-updateteradata
dragonachu
dragonachu

Reputation: 551

Teradata update with left join and inner join

I need to bring the below sample code from mssql to Teradata. Please let me know how to convert it.Sample code - 

Update table1
set table1.name = table3.name 
from table1 
inner join table2 
on table2.id = table1.id 
left join table3 
on table2.id = table3.id where table3.name is null

Upvotes: 1

Views: 5788

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

I think your logic is better handled by not exists:

Update table1
    set table1.name = null
    where not exists (select 1
                      from table2 join
                           table3 
                           on table2.id = table3.id
                      where table2.id = table1.id 
                     );

This is not exactly what your query specifies -- this will update table1.name when there is no match in table2. If that is an issue, you can do:

update table1
    set table1.name = null
    where exists (select 1
                  from table2
                  where table2.id = table3.id
                 ) and
          not exists (select 1
                      from table2 join
                           table3 
                           on table2.id = table3.id
                      where table2.id = table1.id 
                     );

Upvotes: 0

ravioli
ravioli

Reputation: 3833

Like the others mentioned, you should check your NULL condition. Nevertheless, here's one more option:

Update table1
FROM (
  select table1.id, table3.name
  from table1 
  inner join table2 
  on table2.id = table1.id 
  left join table3 
  on table2.id = table3.id where table3.name is null
) src
SET table1.name = src.name
WHERE table1.id = src.id

You just move your original source query into a "src" sub-query and update from it. Just make sure in your "src" query you only have one row for each table1.id value so you don't get target row updated by multiple source rows errors.

Upvotes: 0

Andrew
Andrew

Reputation: 8758

It's ugly, but this should work. You can get around Teradata not allowing outer joins in an update by using a derived table.

update table1
from table1,
(select <column list> from table2 left join table3 on table2.id = table3.id) t
set ...
where
table1.id = t.id
and t.name is null

Upvotes: 2

Related Questions