CODEWITHSUNDEEP
sqlt-sqlmergesybase-ase15
Horaciux
Horaciux

Reputation: 6477

SQL Merge. Update when exits, insert if it doesn't, using variables in stored procedure

Never used MERGE before nor Sybase ASE 15.7

I need a stored procedure for updating existing data or insert it if it doesn't exist. I know how to do it without MERGE but I want to learn to use it.

This is my table and test data:

CREATE TABLE myTable(
    col1    Smallint    Not NULL,
    col2    Char(15)    Not NULL,
    col3    Char(2)     Not NULL,
    col4  BIT   Not NULL,
    col5 varchar(100) NULL,
    Constraint PK_myTable primary key clustered (col1,col2,col3)
)
go

insert into myTable values( 1,'A','1',1,'A')
go

My procedure

    create procedure spInsertUpdateMytable(
        @col1   Smallint,
        @col2   Char(15),
        @col3   Char(2),
        @col4   BIT,
        @col5   varchar(100))
    AS

    merge into myTable as V
         using (select col1, col2, col3, col4, col5 from myTable
              where @col1=col1 and @col2 = col2 and @col3=col3) as N
         ON v.col1=n.col1 and v.col2=n.col2 and v.col3=n.col3
         when not matched then
            insert (col1, col2, col3, col4, col5)
             values( @col1, @col2, @col3, @col4, @col5)
         when matched
            then update set
              v.col4 = @col4, v.col5 = @col5
go

update seems to work, but insert does not.

exec spInsertUpdateMytable  1,'A','1',0,'B' --Update is done ok.

exec spInsertUpdateMytable  1,'C','1',0,'Z' --No new record added

I can't realize what I'm doing wrong, I'm following this specs Adaptive Server Enterprise 15.7 > Reference Manual: Commands > Commands

Upvotes: 3

Views: 840

Answers (1)

Horaciux
Horaciux

Reputation: 6477

Just made the change @PeterHenell commented and it woked.

    create procedure spInsertUpdateMytable(
        @col1   Smallint,
        @col2   Char(15),
        @col3   Char(2),
        @col4   BIT,
        @col5   varchar(100))
    AS

    merge into myTable as V
         using (select @col1 col1, @col2 col2, @col3 col3, @col4 col4, @col5 col5 ) as N

         when not matched then
            insert (col1, col2, col3, col4, col5)
             values( @col1, @col2, @col3, @col4, @col5)
         when matched
            then update set
              v.col4 = @col4, v.col5 = @col5
go

Upvotes: 2

Related Questions