update sql in Oracle conversion from Sql Server

Question

I am trying to create an update statement in the 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

Answer 1

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.

Answer 2

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.