Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
Like Tree3Likes
  • 1 Post By Ronald Roe
  • 1 Post By TheGAME1264
  • 1 Post By Ronald Roe

Thread: MySQL Alternate Insert Syntax

  1. #1
    Senior Member Ronald Roe's Avatar
    Join Date
    Mar 2011
    Location
    Oklahoma City
    Posts
    3,141
    Member #
    27197
    Liked
    959 times

    MySQL Alternate Insert Syntax

    So, the other day I was wondering why SQL's INSERT and UPDATE syntaxes are different. To me, the UPDATE syntax is far more intuitive:

    UPDATE tbl_name SET this='that', some_other_column='new infos' WHERE some_condition=true;

    INSERT is a little less intuitive:

    INSERT INTO tbl_name (this, some_other_column) VALUES ('that', 'new_infos');


    Turns out, MySQL extends the standard SQL to include UPDATE-like syntax for INSERT:

    INSERT INTO tbl_name SET this='that', some_other_column;

    Why does this matter? Aside from the facts that it's a better design for the syntax and it's better to have them both the same since they do similar things, imagine building out queries in a function

    PHP Code:
    $sql '';

    if(
    record_exists){
      
    $sql .= 'UPDATE';
    } else {
      
    $sql .= 'INSERT INTO';
    }

    // build the rest of the query, then if record exists add the WHERE statement 
    IMHO, much better than how I've been doing it, which is to test if record exists, then branch the entire query building process. Would come out as way fewer lines of code, too.
    TheGAME1264 likes this.
    Ron Roe
    Web Developer
    "If every app were designed using the same design template, oh wait...Bootstrap."

  2.  

  3. #2
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    The number of lines of code would depend on how you did it. For example, an SQL query in a stored procedure usually isn't a concatenated string but rather a command...so you probably wouldn't gain much if anything there. Now, if someone comes up with a way where say "if record exists else new" (query) using your syntax, that would be freakin' awesome.

    I'm not sure one's really more intuitive than the other. Then again, I'm so used to doing both that I wouldn't even notice.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  4. #3
    Senior Member Ronald Roe's Avatar
    Join Date
    Mar 2011
    Location
    Oklahoma City
    Posts
    3,141
    Member #
    27197
    Liked
    959 times
    It'll also depend on what tech you're using as well.

    Most other databases have an "upsert" command. It's possible to create a SQL query that will check if an insert is going to "fail" by pushing a record that exists, but that requires you to push the primary key with the insert, which won't work if you're using an autonumber for the key.

    The function I'm trying to create would be used where if I don't send an ID with it, the function would know that it's an INSERT, but if it receives an ID, it queries to verify that it exists and then creates the UPDATE instead. This sounds a lot like the built-in method, but would work with autonumbers as well.
    Ron Roe
    Web Developer
    "If every app were designed using the same design template, oh wait...Bootstrap."

  5. #4
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    I'm not sure if this is the case with MySQL, but with SQL Server you can actually push a primary key as an identity (autonumber) field with an insert. Before your query, you add SET IDENTITY_INSERT ON; I don't use it much, but it comes in handy every 3-6 months.

    The way I typically do it otherwise in pseudocode is to say "if this is an insert, check to see if the record exists. If it doesn't, insert. If it does, update whichever fields can be overwritten."
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  6. #5
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    By the way, I just realized something...this probably falls under the category of "thread that will completely confuse most of the members on here." We broke the webdesignerintarwebz!
    Ronald Roe likes this.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  7. #6
    Senior Member Ronald Roe's Avatar
    Join Date
    Mar 2011
    Location
    Oklahoma City
    Posts
    3,141
    Member #
    27197
    Liked
    959 times
    Quote Originally Posted by TheGAME1264 View Post
    I'm not sure if this is the case with MySQL, but with SQL Server you can actually push a primary key as an identity (autonumber) field with an insert. Before your query, you add SET IDENTITY_INSERT ON; I don't use it much, but it comes in handy every 3-6 months.

    The way I typically do it otherwise in pseudocode is to say "if this is an insert, check to see if the record exists. If it doesn't, insert. If it does, update whichever fields can be overwritten."
    That's how an upsert works. There's no good native way to do that in base SQL. It sounds like T-SQL (MSSQL) has added it.

    Also, yes. We're the biggest nerds on the block.
    TheGAME1264 likes this.
    Ron Roe
    Web Developer
    "If every app were designed using the same design template, oh wait...Bootstrap."


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:08 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com