Upsert (update or insert) in Sybase ASE? Upsert (update or insert) in Sybase ASE? sql sql

Upsert (update or insert) in Sybase ASE?

unfortunately, it is impossible to insert and update a table in one statement without using MERGE. which btw does exist in SQL as of SQL:2008, according to this article anyway, and supported by almost all major databases, except Sybase ASE and PostgreSQL.

Sybase and DB2 are very IEC/ISO/ANSI SQL Standrd-compliant. MS a little less so.

Oracle is not very Standard-compliant at all (despite what the glossies say). More important, due to it limitations, the method they use to overcome them is to introduce Extensions to SQL (which are not required for the others DBMS, which do not have the limitations). Nice way of making sure that customers do not migrate away.

So the best advice for you is to learn the SQL Standard way of doing whatever you were doing on the Oracle side. And second (not first) learn about Sybases or DB2s (or whatever) Extensions.

"MERGE" and "UPSERT" do not exist in SQL, they exist in Oracle only. The bottom line is, you have to UPDATE and INSERT in two separate operations.

In SQL, UPDATE and INSERT apply to a single table; you may have quite complex FROM clauses.

For "MERGE", that is simply an:

INSERT target ( column_list ) -- we do have defaultsSELECT ( column_list )    FROM source    WHERE primary_key NOT IN ( SELECT primary_key FROM target )

Update is simply the complement:

UPDATE target SET ( target_column = source_column, ... )    FROM source    WHERE primary_key IN ( SELECT primary_key FROM target )

In the UPDATE it is easy to merge the WHERE conditions and eliminate the Subquery (I am showing it to you for explanation).

As I understand it, Oracle is abyssmal at executing Subqueries (Standard SQL). Which is why they have all these non-Standard "MERGE", etc., the purpose of which is to avoid the Standard Subquery syntax, which every other DBMS performs with ease.