How to UPSERT a record in SAP ASE Sybase 16?

Question

I am literaly following the SAP documentation 1st example on UPSERT a record in ASE: https://help.sap.com/viewer/cbed2190ee2d4486b0bbe0e75bf4b636/16.0.3.2/en-US/faf583d9adc547ad8a164bb3f41ea6cd.html

1> select @@version
2> go
Adaptive Server Enterprise/16.0 SP03 PL06/EBF 28334 SMP/P/x86_64/SLES 11.1/ase1
60sp03pl06x/3457/64-bit/FBO/Mon Nov 26 04:33:30 2018
(1 row affected)

1> select * from t1
2> go
 a           b           c
 ----------- ----------- -----------
           1           2           3
(1 row affected)

1> upsert t1(a,b,c) values(1,2,3)
2> go
Msg 102, Level 15, State 181:
Server 'NPL', Line 1:
Incorrect syntax near 'a'.

Does anyone know why am I getting this "Incorrect syntax" error in the UPSERT statment? Thanks

Answer 1

If the sole intent is to implement upsert capability in ASE then what you want to look at is the merge command.

If the intent is to utilize (a subset of) HANA's SQLScript (in this case the upsert command) for some sort of interoperability requirement, and keeping in mind you may need to modify existing code to work with dual (and incompatible) parsers, then ...

---

To use (a limited version of) HANA's SQLScript in ASE you first need to create a database that supports the SQLScript parser (see Creating a SQLScript database), eg:

use master
go
create database sqlscript_db
    on data_01=10
log on log_01=5
for sqlscript                  -- enable use of SQLScript parser
go

Running sp_helpdb (from a non-SQLScript db) to verify db status:

use master
go
sp_helpdb sqlscript_db
go

 name         db_size       owner dbid created      durability lobcomplvl inrowlen status
 ------------ ------------- ----- ---- ------------ ---------- ---------- -------- ---------
 sqlscript_db       15.0 MB sa       7 Mar 25, 2022 full                0     NULL sqlscript
... snip ...                                                                       ^^^^^^^^^

You should now be able to use the upsert statement in this new database:

use sqlscript_db
go
create table t1 (a int, b int, c int)
go
upsert t1(a,b,c) values(1,2,3)
go
(1 row affected)

select * from t1
go

 a           b           c
 ----------- ----------- -----------
           1           2           3

NOTE: verified on ASE 16.0 SP04 GA

---

Verifying SQLScript/upsert does not work in a non-SQLScript database:

use tempdb
go
create table t1 (a int, b int, c int)
go
upsert t1(a,b,c) values(1,1,1)
go

Msg 102, Level 15, State 181:
Server 'ASE400', Line 1:
Incorrect syntax near 'a'.

Answer 2

if your unique key, let's say, is t1.a, then you can use the logic below:

merge into t1 as dest
using (select 1 a, 2 b, 3 c) as src
on dest.a = src.a
when not matched then
  insert (a,b,c) values(src.a,src.b,src.c)
when matched then
  update set b=src.b, c=src.c