0

I wanted to know how to write this query in Oracle SQL:

UPDATE address 
SET    phone1 = sp.phone, 
       is_avlbl = ( CASE 
                      WHEN sp.name IS NULL THEN 1 
                      ELSE 0 
                    END ) 
FROM   address ad 
       LEFT JOIN speaker sp 
              ON sp.addressid = ad.id

The above query format is from MS SQL Server but I want to achieve similar functionality with Oracle.

Already seen Update and left outer join statements, which is for T-SQL.

EDIT

I have tried the following solution:

update 
  table1 t1
set
  (
    t1.column1, 
    t1.column2,
    t1.column3
      ) = (
    select
      t2.column1, 
      t2.column2,
      ( CASE 
           WHEN t2.column2 IS NULL THEN 1
              ELSE 0 
              END ) 
    from
      table2  t2
    where
      t2.column1 = t1.column1
     );

But the problem is that When there is no record in t2 corresponding to t1, then the above sql inserts null values into t1 where as i need some other value inserted into it when there is no such record. I apologize if this part of the requirement was not clear earlier.

Community
  • 1
  • 1
MozenRath
  • 9,652
  • 13
  • 61
  • 104
  • Please Refer http://stackoverflow.com/questions/7918688/update-rows-in-one-table-with-data-from-another-table-based-on-one-column-in-eac – Ashutosh Arya Aug 27 '13 at 09:53
  • possible duplicate of [Oracle - Update statement with inner join](http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join) – Ben Aug 27 '13 at 09:53
  • I have checked that question but the issue is that I am unable to use that strategy here... Will edit Question shortly – MozenRath Aug 27 '13 at 11:20

1 Answers1

1

Something like this maybe:

merge into address
using 
(
   SELECT ad.id, 
          sp.phone, 
          sp.name 
   FROM address ad 
     LEFT JOIN speaker sp ON sp.addressid = ad.id 
) t on (address.id = t.id)
when matched then update 
     set phone1 = t.phone,
         is_avlbl = case 
                       when t.name is null then 1
                       else 0
                    end;

(This assumes that address.id is the primary key)

Not tested though, there might be typos that cause syntax errors.

  • I know of this solution but i think it will have a very high cost and bytes – MozenRath Aug 27 '13 at 17:57
  • @MozenRath: did you check the execution plan? Or did you run it with autotrace? How do you know it's more costly than a simple `UPDATE`. Don't "assume" things like that. Test and verify. –  Aug 27 '13 at 18:05
  • actually i did and the costs doubled – MozenRath Aug 28 '13 at 11:31