SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast Roj2002's Avatar
    Join Date
    Jan 2002
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get affected row-ids

    I'm using a postgres database.

    I make a "UPDATE" query like this:

    UPDATE table SET sort = sort - 1 WHERE sort > '10'

    The problem is, I need to get a recordset back with the ids of the affected rows. Can this be done?

    Roj

  2. #2
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cannot be done in the update query. there is simple work around for it.

    This statment would give you the result you are looking for:

    SELECT * FROM table WHERE sort > 9

    Cheers,
    Shalin
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shalin
    Cannot be done in the update query. there is simple work around for it.

    This statment would give you the result you are looking for:

    SELECT * FROM table WHERE sort > 9

    Cheers,
    Shalin
    That'll return the rows which were affected, however it'll also return all the rows which were where sort were already equal to 10.
    Regards, Ant.

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Depends on your DBMS, but something like this:
    Code:
    BEGIN TRANSACTION
      SELECT ID FROM table WHERE sort > 10
      UPDATE table ...
    COMMIT TRANSACTION


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
  •