CODEWITHSUNDEEP
sqloracle-databasesql-update
Greener
Greener

Reputation: 477

Update SQL in Oracle conversion from SQL Server

I am trying to create an update statement in Oracle. Here it is version in SQL Server: 

  UPDATE ev
  SET   
     (ev.station_type = t.st_type, ev.adt = t.adt  )
  FROM  source ev 
  JOIN  dataTbl t
  ON ev.counterID = t.counterID

There are two tables source table were counterID is primary key dataTBL table were counterID is Foreign key I am tring to get data from dataTBL to souce table.

I would appreciate if someone helps to create an Oracle version of the update. Thanks, Greener

Upvotes: 0

Views: 716

Answers (2)

Alex Martelli
Alex Martelli

Reputation: 882291

I believe the following syntax would work (no Oracle at hand to check):

UPDATE ev
SET (station_type, adt ) = (
   SELECT (t.st_type, t.adt)
   FROM dataTbl t
   WHERE t.counterId = ev.counterId
)
WHERE ev.counterId IN (SELECT t.counterID FROM t);

Edit: the WHERE clause repetition looks ugly, but as @Steve points out it's needed to avoid inserting nulls in rows that have no matching entry in dataTbl (if that's a possibility in your use case, of course). If you do need such precautions, @Steve's join-based solution may be preferable.

Upvotes: 0

Steve Broberg
Steve Broberg

Reputation: 4394

You want to use this technique:

  UPDATE ( SELECT ev.station_type
                , t.st_type
                , ev.adt ev_adt
                , t.adt t_adt
             FROM  source ev
                   JOIN dataTbl t
                     ON ev.counterID = t.counterID
         )
     SET station_type = st_type
       , ev_adt = t_adt
  ;

The subquery creates your resultset, at which point you can use the SET clause to update the columns however you wish.

Upvotes: 2

Related Questions