11

I'm writing an application to move data from Oracle to Sybase and need to perform update / insert operations. In Oracle, I'd use MERGE INTO, but it doesn't seem to be available in Sybase (not in ASE, anyway). I know this can be done with multiple statements, but for a couple of reasons, I'm really trying to get this into a single statement.

Any suggestions?

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Ickster
  • 2,167
  • 4
  • 22
  • 43

6 Answers6

7

ASE 15.7 has this feature.

Find the docs here: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36272.1570/html/commands/commands84.htm

Community
  • 1
  • 1
Ritter
  • 79
  • 1
  • 1
6

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 defaults
SELECT ( 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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • Thanks, but as I indicated in the question, I'm aware of how to do this with individual Insert and Update statements. I'm also aware of ANSI SQL. I was generally looking for suggestions on methods or extensions of which I was not aware to accomplish the process in a single step. I'm not a big fan of Oracle, but I've found the MERGE statement to be a simple and elegant tool in several situations. – Ickster Jan 19 '11 at 22:34
  • 1
    @Ickster. Ok, but the question asked for "single statement". I have provided the only "method". It works for set-processing (multiple rows). The only other "method" is to write it as a stored proc, but that will degrade it to row-processing (very slow). – PerformanceDBA Jan 19 '11 at 22:55
5

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.

vsi
  • 51
  • 2
2

Merge exists in SAP ASE 15.7 upwards, as mentioned here and here

Replace / Upsert exists in SAP ASE 16.0 and up.

You'll need to update to access them.

Zoe
  • 27,060
  • 21
  • 118
  • 148
user11673175
  • 41
  • 2
  • 4
    A link to a solution is welcome, but please ensure your answer is useful without it: [add context around the link](//meta.stackexchange.com/a/8259) so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. [Answers that are little more than a link may be deleted.](//stackoverflow.com/help/deleted-answers) – dippas Jun 19 '19 at 23:59
  • 1
    **This is not a link-only answer**. The question asks for these features in Sybase ASE, the links are used to back up the statement of when it's added, and through that, what version is required to access these. Please keep the context of the question in mind when reviewing, and that unformatted links does not mean the answer is a link-only answer – Zoe Jun 20 '19 at 19:40
  • That being said, @user11673175: please read [the formatting guide](/help/formatting). When you have links you want to use to back up a statement, consider formatting it as a part of text rather than dumping it in plain text. It also avoids misunderstandings from reviewers. And while this answer is an answer, I highly recommend you read [this meta post](https://meta.stackexchange.com/a/8259/332043), as well as [this one](https://meta.stackexchange.com/q/225370/332043) to learn where the line goes between what's considered an answer, and what's considered not an answer. – Zoe Jun 20 '19 at 19:46
  • Note: Replace / Upsert exists on only on SQLScript. Merge does exists in the standard ASE instalation. – ArturoTena Jul 12 '22 at 15:37
1

Maybe it could work. Tested in ASA9.

insert into my_table (columns) on existing update values (values);
Fabrício Benvenutti
  • 3,556
  • 1
  • 9
  • 5
0

May be you could try to fake it with INSERT INTO and/or UPDATE FROM with some sub-queries but it will not be as convenient as Oracle does.

You wanna do this into code or data warehouse ? because you could also encapsulate all the SQL into a stored procedure if you want to hide the complexity of the queries.

Stef
  • 3,691
  • 6
  • 43
  • 58