SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Adjusting Mutiple Rows at Once

    Hi all,

    Within my application I have quite a few products that contain a field that relates to its parent (category) ID. What I now want to do is to adjust the parent_id value for a majority of these products, but instead of adjusting each row individually, I would like to adjust them all in one query if at all possible, as the new parent_id value for these rows will be the same.

    Thanks

    Tryst

  2. #2
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like...
    Code:
    UPDATE tablename SET field = something WHERE field = somethingelse
    Lats...

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But that would mean that I would be updating all rows that had the 'parent_id' specified as the criteria the where clause. My problem is slightly different.

    For example, if I had 5 rows...

    id - parent_id
    1 - 10
    2 - 10
    3 - 10
    4 - 10
    5 - 10

    ...and wanted 3 of those to be changed so that they have a parent_id of 11, then the query you suggested would not be possible as it would change the parent_id value for all the rows.

    Would it be possible to use the 'IN' clause, where I then have all the 'id's' of the products that I wish to adjust?

    Thanks

    Tryst

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by Tryst
    Would it be possible to use the 'IN' clause, where I then have all the 'id's' of the products that I wish to adjust?
    certainly

    Code:
    UPDATE tablename SET parent_id = 11 WHERE id IN (2, 4, 5);
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  5. #5
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well done, more information certainly helps
    Lats...


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
  •