Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: 'UPSERT' Query

  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Exclamation Unanswered: UPDATE IF Exists, Else insert---SOS!!!!

    Hi folks,

    I know this problem is one of the most common ones, but please bear with me. I've googled and yahooed the whole internet, but codes written by other people don't really help me... This is my first experience with working with MS SQL Server 2005.

    I have a table that needs to be updated on regular basis from another table: if the row exists, it is to be updated. If the record doesn't exist, insert a new row.

    I have two tables: T1 and T2. T2 'feeds' T1. The tables have the following structure:

    T1: ReqNr_______ Description
    1 ---------- a
    2 ---------- b
    3 ---------- c

    T2: 2----------- b
    3---------- c
    4------------d


    After the update, T1 should look as follows:

    T1: ReqNr --------- Description
    1 ------------ a
    2 ------------ b
    3 ------------ cc
    4 ------------ d

    In both tables, ReqNr is the primary key.
    The code below doesn't work --- whenever I run the query, SQL Server says that the command was completed succesfully, but nothing happens. Lines in {} mean that there was no difference whether I included or omited them.

    Any suggestions would be greatly appreciated!! Getting really desperate here. :-(

    Code:
    SELECT * FROM Table_1, Table_2
    --check first if the record already exists
    IF EXISTS(SELECT * FROM Table_1 WHERE Table_1.ReqNr = Table_2.ReqNr)
    	--if it does, replace it 
    	BEGIN
    		UPDATE Table_1 SET Table_1.Description = Table_2.Description
    		{WHERE Table_1.ReqNr = Table_2.ReqNr}
    	END
    --if there is no such record yet, append new rows
    ELSE
    	BEGIN
    		INSERT INTO Table_1(ReqNr, Description, FD)
    		SELECT ReqNr, Description, FD FROM Table_2 
    		{WHERE ReqNr NOT IN(SELECT ReqNr FROM Table_2)}
    	END;

    Thanks a lot!!
    OfficeDummy
    Last edited by OfficeDummy; 08-22-08 at 14:00.

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    MERGE function would be nice, as they have in Oracle.

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    How do you use MERGE? But as far as I know, you can't use it in SQL Server...
    Any suggestions, anyone?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INSERT... ON DUPLICATE KEY UPDATE would be nice, as they have in MySQL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by OfficeDummy
    How do you use MERGE? But as far as I know, you can't use it in SQL Server...
    Any suggestions, anyone?

    found this somewhere:


    UPDATE T1
    SET -- You know the column names, not me !
    FROM Table2 T2
    INNER JOIN Table1 T1 ON join condition here
    WHERE T1.Column IS NOT NULL


    INSERT INTO Table1 (Col list here)
    SELECT Column list
    FROM Table2 T2
    LEFT OUTER JOIN Table1 T1 ON Join condition here
    WHERE T1.Column IS NULL -- If no match, insert this row

  6. #6
    Join Date
    Jan 2008
    Posts
    74
    Thank you, PMASchmed.
    I've changed the UPDATE and INSERT Statements, but still nothing happens.
    I run the query from Management Studio.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by PMASchmed
    UPDATE T1
    SET -- You know the column names, not me !
    FROM Table2 T2
    INNER JOIN Table1 T1 ON join condition here
    WHERE T1.Column IS NOT NULL
    The WHERE clause is superfluous for an inner join....

    OfficeDummy, post the exact INSERT and UPDATE statements you used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2008
    Posts
    74
    This is my exact code:

    Code:
    IF EXISTS(SELECT * FROM Table_1, Table_2 WHERE Table_1.ReqNr = Table_2.ReqNr)
    	--if it does, replace it 
    	BEGIN
    		SELECT * FROM Table_1, Table_2
    		UPDATE Table_1 SET Table_1.Description = Table_2.Description
    		FROM Table_1, Table_2 INNER JOIN Table_2 ON Table_1.ReqNr = Table_2.ReqNr
    	END
    --if there is no such record yet, append new rows
    ELSE
    	BEGIN
    		INSERT INTO Table_1 (ReqNr, Description, FD)
    		SELECT ReqNr, Description, FD FROM Table_2
    		LEFT OUTER JOIN Table_1 ON Table_1.ReqNr = Table_2.ReqNr
    	END;
    Management Studio tells that the command was completed succesfully, but Table_1 isn't changed.
    Last edited by OfficeDummy; 08-22-08 at 15:36.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    MERGE has been introduced in SQL 2008. Oddly, the syntax looks almost exactly like the Oracle syntax.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It was in the early 2005 builds but was removed on full release.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2008
    Posts
    74
    All right, my dear experts: the UPDATE part works. However, SQL Server still blissfully ignores the INSERT part:

    Code:
    --check first if the record already exists
    IF EXISTS(SELECT * FROM Table_1, Table_2 WHERE Table_1.ReqNr = Table_2.ReqNr)
    	--if it does, replace it 
    	BEGIN
    		UPDATE Table_1 SET Table_1.Description = Table_2.Description
    		FROM Table_1
    		INNER JOIN Table_2 ON Table_1.ReqNr = Table_2.ReqNr
    	END
    --if there is no such record yet, append new rows
    ELSE
    	BEGIN
    		INSERT INTO Table_1(ReqNr, Description, FD)
    		SELECT Table_2.ReqNr, Table_2.Description, Table_2.FD FROM Table_2
    		LEFT OUTER JOIN Table_1 ON Table_1.ReqNr = Table_2.ReqNr
    	END
    
    SELECT * FROM Table_1;

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the SELECT in your IF will be true whenever any rows match

    i would imagine this will always be the case, consequently the ELSE never fires

    the UPDATE will proceed, and in most cases it will simply update something that's already equal to what it is being updated to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2008
    Posts
    74
    Quote Originally Posted by r937
    the SELECT in your IF will be true whenever any rows match

    i would imagine this will always be the case, consequently the ELSE never fires

    the UPDATE will proceed, and in most cases it will simply update something that's already equal to what it is being updated to
    Thanks! How can I change the IF statement so that only the matching rows update, and the new records actually insert?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    --Update existing records:
    		UPDATE Table_1 SET Table_1.Description = Table_2.Description
    		FROM Table_1
    		INNER JOIN Table_2 ON Table_1.ReqNr = Table_2.ReqNr
    
    --Add new records
    		INSERT INTO Table_1(ReqNr, Description, FD)
    		SELECT Table_2.ReqNr, Table_2.Description, Table_2.FD FROM Table_2
    		LEFT OUTER JOIN Table_1 ON Table_1.ReqNr = Table_2.ReqNr
    		WHERE Table_1.ReqNr is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Notice, no IF statement or WHERE EXISTS is necessary.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •