SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql replace multi-column values

    Hello.

    I have the following table structure:

    Code:
    id    root_id
    1     1
    2     1
    3     1
    4     4
    5     4
    6     4
    I have to update/replace values:
    SET root_id = 4 WHERE root_id = 1
    SET root_id = 1 WHERE root_id = 4
    SET id = 4 WHERE id = 1
    SET id = 1 WHERE id = 4

    basically I want:

    Code:
    id    root_id
    4     4
    2     4
    3     4
    1     1
    5     1
    6     1
    Q1:
    What is the best way to preform such query?
    I would select all ids with matching values and call update query for each. There must be some better solution, with less than 4 queries needed.

    Q2:
    Concept consideration:
    I'm using Doctrine ORM and it has some strange implementation of NestedSet structure. Each root node has own root_id and I want to be able to change the order of root nodes, so I decided to use existing root_id column for ORDER_BY instead of making new column for sort order.
    Any concerns about this approach, if I wont be accessing elements by ids?

    Thanks a lot for help.

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you set a value to 4 where it is currently 1 then, if you set all 4's to 1 as was your other requirement, they will all end up as 1.

    Better, imv, to set value to 1000 where value = 1. then set value = 1 where value = 4. then set value = 4 where value = 1000.

    I hope I understood your question.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    If you set a value to 4 where it is currently 1 then, if you set all 4's to 1 as was your other requirement, they will all end up as 1.
    if you did it in two steps, yes

    however, if you did them both at the same time in a single update statement, mysql updates each row independently, so you could change all 1's to 4's and all 4's to 1's at the same time

    read up on it in da manual, it's really neat

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy,

    I didn't know that so i went for the easier option, not to break my data.
    bazz

  5. #5
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you did it in two steps, yes

    however, if you did them both at the same time in a single update statement, mysql updates each row independently, so you could change all 1's to 4's and all 4's to 1's at the same time

    read up on it in da manual, it's really neat


    Hey,
    The following syntax will give me duplicate primary error:

    Code:
    UPDATE table
      SET id CASE id
      WHEN 1 THEN 2
      WHEN 2 THEN 1
      END
    Do you have any idea how to pass it, without changing the properties of id column?

    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thank you so much for testing it

    it turns out that i was wrong with my information

    yes, i know, shocking, but it does happen occasionally (not since last year, though)

    you'll have to use multiple update statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    May 2006
    Location
    Ljubljana
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    thank you so much for testing it

    it turns out that i was wrong with my information

    yes, i know, shocking, but it does happen occasionally (not since last year, though)

    you'll have to use multiple update statements
    Do you have any idea how to set first (temporary) id to be ensured its unique so that you don't mess up records or need separated select query?


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
  •