Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: update single column with multiple values on multiple rows

    Hello!
    I need some assistance in writing an update sql query. I am having hard time finding this on google or the search feature on this forum. Here is what I would like to update.

    I have a table (TABLE_1) with 100 rows.
    I want to update a single column (STATUS_MSG) on 80% of rows with value 'A'
    10% of rows with value 'B'
    10% of rows with value 'C'

    Is that possible to do in a single sql statement? Any direction would be greatly appreciated.

    thanks,

    Sajid

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by smohammad View Post
    Is that possible to do in a single sql statement?
    yes, it is, using a CASE expression

    but, i mean, why bother?

    you can run three separate queries a lot faster than it would take you to even write the single query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2012
    Posts
    4

    I want to make this re-usable

    Quote Originally Posted by r937 View Post
    yes, it is, using a CASE expression

    but, i mean, why bother?

    you can run three separate queries a lot faster than it would take you to even write the single query
    Thanks for your reply. I want to make this query re-usable. So anyone can just plugin the percentage and the Values and run it. Would you please show me an example of a case with percentage of my total rows.

    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example tested on DB2 9.7.5 on Windows/XP.

    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE
    (SELECT status_msg
          , ROW_NUMBER() OVER() AS row_num
      FROM  test_table_1
    )
       SET status_msg
         = SUBSTR('AAAAAAAABC' , MOD(row_num , 10) + 1 , 1)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COUNT(*) AS count_rows
         , COUNT( CASE status_msg WHEN 'A' THEN 0 END ) AS count_a
         , COUNT( CASE status_msg WHEN 'B' THEN 0 END ) AS count_b
         , COUNT( CASE status_msg WHEN 'C' THEN 0 END ) AS count_c
     FROM  test_table_1
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS  COUNT_A     COUNT_B     COUNT_C    
    ----------- ----------- ----------- -----------
            100          80          10          10
    
      1 record(s) selected.
    Test table was creaed and populated by...
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE test_table_1
    ( id SMALLINT GENERATED ALWAYS AS IDENTITY
    , status_msg  CHAR(1)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO test_table_1
    (status_msg)
    SELECT ''
     FROM  (VALUES 0,1,2,3,4,5,6,7,8,9) n(n1)
         , (VALUES 0,1,2,3,4,5,6,7,8,9) n(n2)
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COUNT(*) AS count_rows
         , MIN(id)  AS min_id
         , MAX(id)  AS max_id
         , MIN(status_msg) AS min_status_msg
         , MAX(status_msg) AS max_status_msg
     FROM test_table_1;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS  MIN_ID MAX_ID MIN_STATUS_MSG MAX_STATUS_MSG
    ----------- ------ ------ -------------- --------------
            100      1    100                              
    
      1 record(s) selected.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice one, tonk

    i obviously misunderstood the question

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... just plugin the percentage and the Values and run it. ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE
    (SELECT status_msg
          , ROW_NUMBER() OVER() AS row_num
      FROM  test_table_1
    )
       SET status_msg
         = CASE
           WHEN MOD(row_num , 100) < 70 THEN /* A: 70% */ 
                'A'
           WHEN MOD(row_num , 100) < 70
                                   + 10 THEN /* B: 10% */
                'B'
           WHEN MOD(row_num , 100) < 70
                                   + 10
                                   +  5 THEN /* C:  5% */
                'C'
           ELSE 'D'                          /* D: 15%(rest of) */
           END
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COUNT(*) AS count_rows
         , COUNT( CASE status_msg WHEN 'A' THEN 0 END ) AS count_a
         , COUNT( CASE status_msg WHEN 'B' THEN 0 END ) AS count_b
         , COUNT( CASE status_msg WHEN 'C' THEN 0 END ) AS count_c
         , COUNT( CASE status_msg WHEN 'D' THEN 0 END ) AS count_d
     FROM  test_table_1
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS  COUNT_A     COUNT_B     COUNT_C     COUNT_D    
    ----------- ----------- ----------- ----------- -----------
            100          70          10           5          15
    
      1 record(s) selected.

  7. #7
    Join Date
    Jul 2012
    Posts
    4

    This is exactly what I was looking for. Thank You.

    Quote Originally Posted by tonkuma View Post
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE
    (SELECT status_msg
          , ROW_NUMBER() OVER() AS row_num
      FROM  test_table_1
    )
       SET status_msg
         = CASE
           WHEN MOD(row_num , 100) < 70 THEN /* A: 70% */ 
                'A'
           WHEN MOD(row_num , 100) < 70
                                   + 10 THEN /* B: 10% */
                'B'
           WHEN MOD(row_num , 100) < 70
                                   + 10
                                   +  5 THEN /* C:  5% */
                'C'
           ELSE 'D'                          /* D: 15%(rest of) */
           END
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT COUNT(*) AS count_rows
         , COUNT( CASE status_msg WHEN 'A' THEN 0 END ) AS count_a
         , COUNT( CASE status_msg WHEN 'B' THEN 0 END ) AS count_b
         , COUNT( CASE status_msg WHEN 'C' THEN 0 END ) AS count_c
         , COUNT( CASE status_msg WHEN 'D' THEN 0 END ) AS count_d
     FROM  test_table_1
    ;
    ------------------------------------------------------------------------------
    
    COUNT_ROWS  COUNT_A     COUNT_B     COUNT_C     COUNT_D    
    ----------- ----------- ----------- ----------- -----------
            100          70          10           5          15
    
      1 record(s) selected.
    Thank you so much. This is exactly what I wanted. Sorry for making you go through all that trouble.

    Regards,
    Sajid

  8. #8
    Join Date
    Jul 2012
    Posts
    4

    strange, it didn't work on Oracle.

    Hi,
    This is really weird, it worked on DB2 for you. When I ran this on Oracle it throws the following error:

    Oracle Database Error: ORA-01732: data manipulation operation not legal on this view

    I also had to add an order by in Over(). This is what I have with my actual table:

    UPDATE
    (SELECT STATUSMSG
    , ROW_NUMBER() OVER(ORDER BY CUST_ID) AS row_num
    FROM PLATE_ADDRESS_REFRESH
    )
    SET STATUSMSG = CASE
    WHEN MOD(row_num , 100) < 70 THEN /* A: 70% */
    'ATTACHED'
    WHEN MOD(row_num , 100) < 70
    + 10 THEN /* B: 10% */
    'WRN262'
    WHEN MOD(row_num , 100) < 70
    + 10
    + 5 THEN /* C: 5% */
    'ERRH02'
    ELSE 'ERRH03' /* D: 15%(rest of) */
    END
    ;

Posting Permissions

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