CODEWITHSUNDEEP
sqloracle-databasesql-update
user2974732
user2974732

Reputation: 103

Oracle: Update based on condition

I want to update a column of table based on a condition. It should check if the value exists in other table, if exists then value from other table will be used else value from same table will be used.

Update table1
Set column1=(select t2.alias||’@email.com’ as new_name
From table2 t2, table1 t1, table3 t3
Where t1.id=t2.id
And t1.id=t3.id

Else if
Select t2.alias is null or t2.id is null 
then column1= select t1.id||’@email.com’ as new_name

Any suggestions on this??

Thanks in advance.

Upvotes: 0

Views: 334

Answers (2)

TommCatt
TommCatt

Reputation: 5636

By leaving out the "insert" half of the merge statement, you can make it into a strictly update statement:

MERGE INTO table1 t
USING(
  SELECT t2.id, t2.Alias
  FROM   table2 t2
  JOIN   table1 t1
    ON   t1.id = t2.id
    AND  t1.Alias <> t2.Alias
) tu
on( tu.id = t.id )
WHEN MATCHED THEN
  update set t.Alias = tu.Alias;

The query will return only those existing values from table2 that differ from table1. To make it exactly match your requirement (update table1 if any value exists in table2) then remove the line AND t1.Alias <> t2.Alias, but why update a field to the same value it already has?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Does this do what you want?

Update table1
Set column1 = (select (case when t2.alias is not null and t2.id is not null then t2.alias
                            else t1.id
                       end) ||'@email.com' as new_name
               From table1 t1 left outer join
                    table2 t2
                    on t1.id=t2.id
              );

I removed table3 because it does not seem to be used. With left join is won't even be filtering any results.

Upvotes: 2

Related Questions