Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

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

Results 1 to 3 of 3
Share This Thread →
  1. #1
    smoth is offline Registered User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  3. #3
    smoth is offline Registered User
    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..

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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, 06:31 PM
  2. MHTMLRedir.Exploit - Revisited
    By Tolik in forum Windows OS
    Replies: 8
    Last Post: August 9th, 2005, 05:18 AM
  3. still having trouble with insert statement
    By sp3 in forum Microsoft Access Help
    Replies: 5
    Last Post: January 20th, 2005, 12:02 PM
  4. help with inset statment
    By sp3 in forum Microsoft Access Help
    Replies: 12
    Last Post: January 19th, 2005, 12:47 PM
  5. retrieve multi dim array value
    By aik_21 in forum ASP Development
    Replies: 4
    Last Post: July 15th, 2004, 07:27 AM

ASP Free Advertisers and Affiliates