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.
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