Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2014
    Posts
    1

    Unanswered: UPDATE only rows which match another table

    I want to update USER column values in a tableA from USER column from table B only for users that have STATUS 6.
    In tableB I can have for the same ID_FROM_TABLE_A more than one record with the same status.

    tableA:

    ID USER
    1 20
    2 30
    3 50


    tableB:

    ID ID_FROM_TABLE_A USER STATUS
    10 1 20 6
    24 1 30 6
    37 1 25 4
    45 2 15 6
    76 2 27 6
    98 2 56 2

    I tried to do something like :

    update tableA set
    a.USER=
    (select b.USER
    from tableB b
    join tableA a on b.ID_FROM_TABLE_A=a.ID
    where b.id in (select min(b.ID)
    from tableB b
    join tableA a on
    b.ID_FROM_TABLE_A=a.ID and b.STATUS=6 group by b.ID_FROM_TABLE_A))


    but it doesn't work

    Can anyone help me?

    Thank you in advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    but it doesn't work
    What did you mean by that?
    Did you got error message(s)?
    or
    The results was different from your required/expected results?


    By he way,
    It might be unnecessary to join tableA in your update statement.

    Add an alias to tableA and reference it in the following clauses, like...
    Code:
    UPDATE tableA AS a
     SET   user
         = (SELECT b.user
             FROM  tableB AS b
             WHERE b.id_from_table_a = a.id
               AND ....
    ...
    ...

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (Q1) ID in tableA and ID in tableB may be unique?
    Is my guess right?


    (Q2) What results do you want, if more data were added, like ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM table_a;
    SELECT * FROM table_b;
    ------------------------------------------------------------------------------
    SELECT * FROM table_a
    
    ID     USER  
    ------ ------
         1     20
         2     30
         3     50
         4     60
    
      4 record(s) selected.
    
    
    SELECT * FROM table_b
    
    ID     ID_FROM_TABLE_A USER   STATUS
    ------ --------------- ------ ------
        10               1     20      6
        24               1     30      6
        37               1     25      4
        45               2     15      6
        76               2     27      6
        98               2     56      2
       113               4     35      4
       117               4     25      2
       118               4     44      6
    
      9 record(s) selected.
    My guess of results were...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM table_a;
    ------------------------------------------------------------------------------
    
    ID     USER  
    ------ ------
         1     20
         2     15
         3     50
         4     44
    
      4 record(s) selected.
    Are the results right?

Posting Permissions

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