CODEWITHSUNDEEP
sqlsql-servermerge
shira stenmetz
shira stenmetz

Reputation: 293

merge in sqlserver - incorrect syntax

I try to execute this transaction:

GO
BEGIN TRAN;
MERGE A AS t 
USING B AS tmp
ON (t.domain = tmp.domain and t.link=tmp.link) 
WHEN NOT MATCHED BY A
    THEN INSERT(ipVal, domain, dateStart, dateUpdate, dateFinish, link) VALUES(tmp.ipVal, tmp.domain, tmp.dateStart, tmp.dateUpdate, tmp.dateFinish, tmp.link)
WHEN MATCHED 
    THEN UPDATE SET t.dateupdate = tmp.dateupdate
WHEN NOT MATCHED BY B
    THEN UPDATE SET t.datefinish="a"
ROLLBACK TRAN;
GO

I took this code from here, But when I tried to execute this I got an error:

Incorrect syntax near 'A'

What can be the problem?

Upvotes: 2

Views: 879

Answers (3)

Drishya1
Drishya1

Reputation: 269

Looks like you are trying to update the date or datetime type column with char or varchar type data which is not convertible to date or datetime type. 'a' is not convertible to date or datetime. Look at the First and the second Merge I have below.If you don't mention when matched or when not matched by source or target, by default it considers Target.

--DROP TABLE #A;
--DROP TABLE #B;

CREATE TABLE #A
    (
        ipval VARCHAR(50)
    , domain CHAR(5)
    , dateStart DATE
    , dateUpdate DATE
    , dateFinish DATE
    , link VARCHAR(50)
    );

CREATE TABLE #B
    (
        ipval VARCHAR(50)
    , domain CHAR(5)
    , dateStart DATE
    , dateUpdate DATE
    , dateFinish DATE
    , link VARCHAR(50)
    );
INSERT  INTO #B
        ( ipval, domain, dateStart, dateUpdate, dateFinish, link )
VALUES  ( '42.130.239.56'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 1, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 5, GETDATE())  -- dateFinish - date
            , 'www.stackoverflow'  -- link - varchar(50)
            ),
        ( '78.188.136.74'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 2, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.msdn'  -- link - varchar(50)
            );

INSERT  INTO #A
        ( ipval, domain, dateStart, dateUpdate, dateFinish, link )
VALUES  ( '30.48.111.20'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 5, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.msdn'  -- link - varchar(50)
            ),
        ( '30.48.111.20'  -- ipval - varchar(50)
            , '.com'  -- domain - char(5)
            , GETDATE()  -- dateStart - date
            , DATEADD(DAY, 5, GETDATE())  -- dateUpdate - date
            , DATEADD(DAY, 10, GETDATE())  -- dateFinish - date
            , 'www.gmail'  -- link - varchar(50)
            );
--First Merge
BEGIN TRAN;
MERGE #A AS T
USING #B AS tmp
ON T.domain = tmp.domain
    AND T.link = tmp.link
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( ipval
            , domain
            , dateStart
            , dateUpdate
            , dateFinish
            , link
            )
    VALUES ( tmp.ipval
            , tmp.domain
            , tmp.dateStart
            , tmp.dateUpdate
            , tmp.dateFinish
            , tmp.link
            )
WHEN MATCHED THEN
    UPDATE SET T.dateUpdate = tmp.dateUpdate
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.dateFinish =''a'';
COMMIT TRAN;
GO


SELECT  *
FROM    #A;
SELECT  *
FROM    #B;
--Second Merge
BEGIN TRAN;
MERGE #A AS T
USING #B AS tmp
ON T.domain = tmp.domain
    AND T.link = tmp.link
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( ipval
           , domain
           , dateStart
           , dateUpdate
           , dateFinish
           , link
           )
    VALUES ( tmp.ipval
           , tmp.domain
           , tmp.dateStart
           , tmp.dateUpdate
           , tmp.dateFinish
           , tmp.link
           )
WHEN MATCHED THEN
    UPDATE SET T.dateUpdate = tmp.dateUpdate
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET T.dateFinish = CAST(GETDATE() AS DATE);
COMMIT TRAN;
GO


SELECT  *
FROM    #A;
SELECT  *
FROM    #B;

Upvotes: 0

gofr1
gofr1

Reputation: 15977

One I just began to use MERGE I use this syntax to understand it right:

MERGE A AS target
USING B AS source
ON (target.domain = source.domain and target.link=source.link) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT(ipVal, domain, dateStart, dateUpdate, dateFinish, link)
    VALUES(source.ipVal, source.domain, source.dateStart, source.dateUpdate, source.dateFinish, source.link)
WHEN MATCHED 
    THEN UPDATE SET target.dateupdate = source.dateupdate
WHEN NOT MATCHED BY SOURCE
    THEN UPDATE SET target.datefinish="a"

I know it is a bad practice but when started - helps a lot.

As @BogdanSahlean points TARGET and SOURCE are keywords and there are required when you determine matched/not matched statements.

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

According to MSDN the simplified syntax for WHEN NOT MATCHED branches is

   [ WHEN NOT MATCHED [ BY TARGET ] [...]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [...]
        THEN <merge_matched> ] [ ...n ]

The solution is to replace WHEN NOT MATCHED BY A with WHEN NOT MATCHED BY TARGET and WHEN NOT MATCHED BY B with WHEN NOT MATCHED BY SOURCE.

TARGET and SOURCE are T-SQL keywords not placeholders.

Upvotes: 6

Related Questions