#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2010
    Posts
    22
    Rep Power
    0

    ON DUPLICATE KEY UPDATE (but only if value isn't an empty string)


    I have this SQL:
    Code:
    "INSERT INTO tbl_guilds (name,parent) VALUES (@guild, @astroLocation) ON DUPLICATE KEY UPDATE name=@guild, parent=@astroLocation";
    What I want is saying something like:
    Code:
    "INSERT INTO tbl_guilds (name,parent) VALUES (@guild, @astroLocation) ON DUPLICATE KEY UPDATE name=@guild, parent=@astroLocation IF @guild IS NOT NULL";
    Anyone got any suggestions?
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,600
    Rep Power
    278
    look on merge like that

    Code:
    MERGE INTO tbl_guilds B
    USING (
      SELECT (@guild as guiId_New, @astroLocation as astroLocationNew ) E
    ON (B.name= E.guiId_New)
    WHEN MATCHED and E.guiId_New is not null THEN
      UPDATE SET B.parent = E.astroLocationNew
    WHEN NOT MATCHED THEN
      INSERT (B.name, B.parent)
      VALUES (E.guiId, E.astroLocationNew);
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jun 2010
    Posts
    22
    Rep Power
    0
    Sorry, I forgot to say that I'm using MySql 5.0 so MERGE won't work.. Thanks for the reply though.. I might be able to figure something out from it..

Similar Threads

  1. Login procedure with .NET 2.0 & SQL Server 2000
    By mickyjtwin in forum .NET Development
    Replies: 2
    Last Post: December 29th, 2005, 07:31 PM
  2. MHTMLRedir.Exploit - Revisited
    By Tolik in forum Windows OS
    Replies: 8
    Last Post: August 9th, 2005, 06:18 AM
  3. still having trouble with insert statement
    By sp3 in forum Microsoft Access Help
    Replies: 5
    Last Post: January 20th, 2005, 01:02 PM
  4. help with inset statment
    By sp3 in forum Microsoft Access Help
    Replies: 12
    Last Post: January 19th, 2005, 01:47 PM
  5. retrieve multi dim array value
    By aik_21 in forum ASP Development
    Replies: 4
    Last Post: July 15th, 2004, 08:27 AM

IMN logo majestic logo threadwatch logo seochat tools logo