SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,813
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)

    Insert and update in ONE query?

    Is there a way to INSERT a record, and update other record records in THE SAME TABLE, BASED ON A SET CRITERIA, all in ONE QUERY?


    for example... I want to insert a record that contains :
    RecID, Order, RecName, OtherField5, OtherField5,OtherField5,OtherField5,OtherField5

    while at the same time all updating Order to Order+1 in all rows where Order> the inserted Order.

    I am dealing with a MyISAM DB, and cant use transactions so I was wondering if this simple procedure could be done in ONE query so as to prevent data corruption.



    thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the answer is no

    also, if you are trying to do something like "updating Order to Order+1 in all rows where Order> the inserted Order" then you need to rethink what you're doing, because sequential numbers should not be managed like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,813
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    thanks r937,
    one question then, how would you represents a user-defined sequence of #s this independent of mainKey or order of insertion.

    In other words this column would represent a custom retrieval order. and what i was thinking is that if a user wanted to insert a row after, say, custom position 5, any custom order >= 5 needs to be increased by 1. If there is a better method I would be glad to implement it. What would you suggest?

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,279
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by dresden_phoenix View Post
    thanks r937,
    one question then, how would you represents a user-defined sequence of #s this independent of mainKey or order of insertion.

    In other words this column would represent a custom retrieval order. and what i was thinking is that if a user wanted to insert a row after, say, custom position 5, any custom order >= 5 needs to be increased by 1. If there is a better method I would be glad to implement it. What would you suggest?
    That's a sort order - I would make your UI change the sort order value via code, then just update the SQL table accordingly.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  5. #5
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,813
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    That's a sort order - I would make your UI change the sort order value via code, then just update the SQL table accordingly.....
    how so?

    if a table contains 9 record, and a user is inserting a 10th, which he wants to be ordered as the 5th record in the table when displayed. There must be a colum co contain this data for the SORT function, when the 10 records are retrieved. That the column I am trying to target IN ONE QUERY

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dresden_phoenix View Post
    If there is a better method I would be glad to implement it. What would you suggest?
    i would suggest floating point numbers

    when a new row is added somewhere within the user-defined sort order, or when an existing row is "moved" to a new position within the user-defined sort order, these actions are always accomplished by the front end application logic by designating another already existing row that the new or moved row will be inserted or moved after

    maybe i didn't say that too well, let me know if it didn't make sense

    anyhow, you would find the custom position (floating point number) of both the row before and the row after where the new row is supposed to end up, and you average these two numbers, so the new row goes half way between them

    and because of the way floating point numbers work, you can (if necessary, but it would rarely occur in practice) keep halving the distance as many times as you want

    this way you never have to mass-update all the rows that come after the insertion point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,813
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    when a new row is added somewhere within the user-defined sort order, or when an existing row is "moved" to a new position within the user-defined sort order, these actions are always accomplished by the front end application logic
    yes, it is. The user has a SELECT with the #0(none) -> the record count(say 9, in my example). The choice is to INSERT AFTER.

    so currently I am accomplishing this with TWO queries. The first updates any row with the user defined order > (in my example, > 5) than the selected order, the second inserts the new row, giving it the order selected ( in my example , 5)

    Remember this part is the UI to a CMS, so the DB MUST REMEMBER what order the user has selected for each article. so as to be able to use it on an output template.

    Ok am going to admit your solution sounds brilliant, but so brilliant I am having problems visualizing this.

    Writing the response, I think I see what you mean. how durable could this be if I had, say 10 of 1000s or records? I mean it seems like after just a few changes you would have numbers like e^10-26 or or something.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let's say you start off your table with all new records, and you decide to assign the custom position numbers as follows --

    a 1.0
    b 2.0
    c 3.0
    x 4.0
    g 5.0
    n 6.0

    now you insert a new row p after c

    a 1.0
    b 2.0
    c 3.0
    p 3.5
    x 4.0
    g 5.0
    n 6.0

    now you move b to after p

    a 1.0
    c 3.0
    p 3.5
    b 3.75
    x 4.0
    g 5.0
    n 6.0

    now you add a new row h after b

    a 1.0
    c 3.0
    p 3.5
    b 3.75
    h 3.875
    x 4.0
    g 5.0
    n 6.0

    you can see where i'm going, yes?

    how many times would you have to insert a new custom number after the latest insertion point (i.e. before 4.0) before you run out of significant digits?

    okay, now imagine you started off your custom position numbers incrementing by 10000 instead of 1... now how many insertions can you do?

    how many significant digits can you define for a floating point number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Community Advisor silver trophybronze trophy
    dresden_phoenix's Avatar
    Join Date
    Jun 2008
    Location
    Madison, WI
    Posts
    2,813
    Mentioned
    34 Post(s)
    Tagged
    2 Thread(s)
    I see it now. very clever. I am going to start implementing this! Thanks for the lateral thinking


Bookmarks

Posting Permissions

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