Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    5

    Unanswered: DB2 SQL "Update/Fetch"

    All:
    I am trying to formulate a DB2 SQL queryVer 7.0,fixpack 3,OS:Win200)
    Requirement. Need to pick a 100 records for a particular Column value & change the value.
    Query I need:
    "update s_contact_xm set x_suppress_reason='CM' where row_id in (Select row_id from s_contact_xm where x_suppress_reason='DD' fetch first 100 rows only)

    Suggestions/workarounds appreciated

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try this:

    UPDATE
    s_contact_xm a
    SET a.x_suppress_reason='CM'
    WHERE a.row_id in
    (
    SELECT
    b.row_id
    FROM s_contact_xm b
    WHERE b.x_suppress_reason='DD' fetch first 100 rows only
    )

    This assumes that row_id is unique.

    SQL Formatting courtesey of: http://www.sqlformatter.de/
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Posts
    5
    Dear Marcus: Thank you for taking the time.It returned this error:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "UPDATE s_contact_xm a SET a.x_suppress" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<query_expr>". SQLSTATE=42601
    ---------------------------------------------------------
    Originally posted by Marcus_A
    Try this:

    UPDATE
    s_contact_xm a
    SET a.x_suppress_reason='CM'
    WHERE a.row_id in
    (
    SELECT
    b.row_id
    FROM s_contact_xm b
    WHERE b.x_suppress_reason='DD' fetch first 100 rows only
    )

    This assumes that row_id is unique.

    SQL Formatting courtesey of: http://www.sqlformatter.de/

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I created a table just like yours and loaded it with data. I then ran the exact statement I posted via the DB2 Command Center and it worked. If you submit from the command line (CLI), you may need to enclose the statement in double quotes (") at both ends .
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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