Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: Writing a query that inserts if value doesn't exist; update if it does

    Most of the examples for the INSERT WHERE NOT EXISTS query has been in mySQL, and I'm too new to SQL 2008 to understand the differences. Here is the sample I've run across:

    Code:
    INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
    WHERE NOT EXISTS(
        SELECT [column1] FROM [same table name]
        WHERE [column1]='[value1]'
        AND [column2]='[value2]' LIMIT 1
    )
    For instance, I'm not sure what FROM DUAL is or if it even works in SQL 2008. Any help (helping) me decode this statement would be much appreciated.
    Last edited by westwolf2010; 03-24-10 at 11:15. Reason: Hello forums!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    MERGE might be more appropriate since you are using 2008, but that would just add more complexity!

    FROM DUAL just means the data that is inserted comes from a table called DUAL. The LIMIT 1 is unnecessary (indeed invalid, but also unnecessary) in SQL Server.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sql server has a proprietary extension to standard sql in that it does not require a FROM clause, it allows you to select values from thin air, as it were

    therefore, this is a valid sql server query --
    Code:
    SELECT '[value1]', '[value2]'
    oracle has always had a DUAL table, which consists of exactly one row

    thus, you can say
    Code:
    SELECT '[value1]', '[value2]' FROM DUAL
    here the same values are returned -- nothing in standard sql says you actually have to retrieve any of the columns from the table you mention in the FROM clause

    (aside #1: bonus marks if you know the names of the columns in oracle's DUAL table)

    (aside #1a: extra marks if you know ~why~ the table is called "DUAL")

    (aside #2: are you a disgruntled DBA about to leave an oracle job? add a second row to DUAL and watch a whole whack of apps die...)

    mysql has long had a DUAL table, simply for compatibility purposes, for people converting from oracle to mysql who were too lazy to update their queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    I'm sorry I don't quite understand your response. I just want to check if a specific record already exists. If it does I want to update it, if it doesn't I want to insert it, and I can't use MERGE because the server is running SQL 2000.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by westwolf2010 View Post
    I'm too new to SQL 2008
    Well that bit suggests otherwise - so this is definitely 2000?

    Another informal term for this is UPSERT.

    Code:
    --Insert rows that don't already exist
    INSERT INTO destination_table (column1, column2)
    SELECT  '[value1]'
          , '[value2]'
    WHERE   NOT EXISTS  (
                            SELECT  * 
                            FROM    destination_table
                            WHERE   column1 =   '[value1]'  --Note we removed column2 from the WHERE clause since for
                                                            --this example we assume here that column1 uniquely identifies the row
                        )
    
    --If this function returns 0 then no rows were inserted, which mean they already exist, which means we need to update.
    IF @@ROWCOUNT = 0 
    BEGIN
        UPDATE  destination_table
        SET     column2 =   '[value2]'
        WHERE   column1 =   '[value1]'--We assume here that column1 uniquely identifies the row
    END

Posting Permissions

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