Search code examples
sqlmergesap-aseupsertsybase-ase15

Merge query is failing on sybase 16.0 version

I am trying to run below merge query in sybase 16.0 but it's failing with error

SQL Error [102] [42000]: incorrect syntax near '('.

merge into account_balance( id , account_name) as G 
using SELECT (?, ?) AS D( id , account_name) 
ON D.account_number=G.account_number 
when not matched then 
insert ( id , account_name) values ( D.id , D.account_name)
when matched then 
update set G.id = D.id,G.account_name = D.account_name

What does this error mean ? is this command not supported in sybase 16.0 version but documentation says otherwise.

Solution

  • Assuming OP is using Sybase (now SAP) ASE ...

    tl;dr - the documentation is wrong.

    Sybase ASE documentation was always a bit of hit-or-miss; after SAP bought Sybase they (SAP) have managed to escalate the term 'crappy documentation' to new heights; case in point, the merge statement ...

    NOTE: the following was derived from a) reviewing the merge statement in other RDBMS products and b) some trial-n-error testing.

    First issue: there is no column list in the into clause; column references are handled by other clauses:

    --------
-- replace this:

merge into account_balance( id , account_name) as G

---------
-- with this:

merge into account_balance as G

    Second issue: documentation uses ? to (not so clearly) designate the location where actual data values are to be supplied; these are the values that will be used in the resulting insert or update:

    --------
-- replace this:

using SELECT (?, ?)

---------
-- with this:

using SELECT (3, 'name3')

    Third issue: when using a derived table in the using clause it is necessary to wrap the derived table (aka sub-query) in parens; also, the data values are not wrapped in parens:

    --------
-- replace this:

using SELECT (3, 'name3') AS D( id , account_name)

---------
-- with this:

using (SELECT 3, 'name3') AS D( id , account_name)

    Fourth issue: columns referenced in the on clause must be defined in the using clause (OP's derived table in this case); in this case we need to provide a value and column reference for the join column account_number:

    --------
-- replace this:

using (SELECT 3, 'name3') AS D( id , account_name)

---------
-- with this:

using (SELECT 3, 'name3', 'acct3') AS D( id , account_name, account_number)

    I'm going to assume OP wants to populate the account_number column so the insert clause will need to be expanded to include the account_number column:

    --------
-- replace this:

insert ( id , account_name) values ( D.id , D.account_name)

---------
-- with this:

insert ( id , account_name, account_number) values ( D.id , D.account_name, D.account_number)

    Pulling these changes together we get the following merge statement:

    merge into account_balance as G
using (SELECT 3,'name3','acct3') AS D (id,account_name,account_number)
ON    D.account_number = G.account_number
when  not matched then 
      insert (  id,  account_name,  account_number)
      values (D.id,D.account_name,D.account_number)
when  matched then
      update set G.id           = D.id, 
                 G.account_name = D.account_name

    Taking for a test drive:

    Create our table:

    create table account_balance
(id             int
,account_name   varchar(100)
,account_number varchar(100)
)
go

select * from account_balance
go

 id account_name account_number 
 -- ------------ -------------- 

(0 rows affected)

    merge that results in an insert:

    merge into account_balance as G
using (SELECT 3,'name3','acct3') AS D (id,account_name,account_number)
ON    D.account_number = G.account_number
when  not matched then 
      insert (  id,  account_name,  account_number)
      values (D.id,D.account_name,D.account_number)
when  matched then
      update set G.id           = D.id, 
                 G.account_name = D.account_name
go

select * from account_balance
go

 id account_name account_number 
 -- ------------ -------------- 
  3 name3        acct3

(1 row affected)

    merge that results in an update (id: 3 => 5; account_name: name3 => name5):

    merge into account_balance as G
using (SELECT 5,'name5','acct3') AS D (id,account_name,account_number)
ON    D.account_number = G.account_number
when  not matched then 
      insert (  id,  account_name,  account_number)
      values (D.id,D.account_name,D.account_number)
when  matched then
      update set G.id           = D.id, 
                 G.account_name = D.account_name
go

select * from account_balance
go

 id account_name account_number 
 -- ------------ -------------- 
  5 name5        acct3

(1 row affected)

    NOTE: tested with ASE 16.0 SP04 PL04