Merge in PROC SQL/Libname

[SASAna]

Hello, is there a proc sql alternative of Oracle Merge Statement?

http://www.oracletutorial.com/oracle-basics/oracle-merge/

I'm trying to update Oracle table using value in SAS dataset within proc sql. There are multiple values to be updated . As of now, the query looks like below.

Update oracle_tbl

   set value_1 = (select value_1 from sas_dataset where id = 1) ,

         value_2 = (select value_2 from sas_dataset where id = 1)

where exists( ) and other filters.

I was wondering if there is a way to speed up performance. Please suggest if any. Thanks

[Reeza]

You'll get the fastest performance, either with: 

1) SQL Passthrough since the code is literally just transferred to the server - and would have to be Oracle SQL anyways

2) Can you describe some more? There may be a faster SAS way to update this. 

[ChrisNZ]

A few considerations:

The fastest is to upload your update table to Oracle into a temp space, and run the update query there using pass-through.

If that's not possible, the next fastest is to delete the old rows, and append the new ones. 

Updating one row at a time will always be slow.

Look at the options for the Oracle engine. In this case, the bulkload options  and the updatebuff option are probably the ones that matter most.