Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2010
    Posts
    7

    Unanswered: update multiple rows from subquery

    Hi all,

    I am trying to update all the records in a table based on a list of IDs from a query.

    The sub query is called 'filterGiveUp' and it contains some key values 'player_id'. I wish to update a table called 'hemplayernotes' which has the 'player_id' field as its key.

    The following code seems to update EVERY row in 'hemplayernotes' rather than just those in' filterGiveUp'.

    update hemplayernotes set icon_id=2 where exists (select filterGiveUp.player_id from hemplayernotes inner join filterGiveUp on filterGiveUp.player_id=hemplayernotes.player_id);

    help please

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by simaomacaco View Post
    The sub query is called 'filterGiveUp' and it contains some key values 'player_id'. I wish to update a table called 'hemplayernotes' which has the 'player_id' field as its key.
    Your example shows a table (or view) named filterGiveUp not a query.

    Seems like you are looking for something like this:
    Code:
    UPDATE hemplayernotes 
       SET icon_id=2 
    WHERE player_id in (SELECT f.player_id 
                        FROM filterGiveUp f);

  3. #3
    Join Date
    Dec 2010
    Posts
    7
    thanks, I have tried that approach but the problem is that the update only seems to affect the one row.


    UPDATE hemplayernotes
    SET icon_id=2
    WHERE player_id in (SELECT f.player_id
    FROM filterGiveUp f);

    'Query returned successfully: 1 row affected, 8272 ms execution time.'

    If I run:

    SELECT * FROM filterGiveUp

    then it returns 29 unique 'player_id'.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Are all 29 player_id's actually in hemplayernotes?

    Btw: for future posts, please use [code] tags (as I did) to make your SQL easier to read.

  5. #5
    Join Date
    Dec 2010
    Posts
    7
    Yes, all the rows have values. The view is based on a number of other queries that generates the set of player_ids.

    If i use the EXISTS keyword then all 800+ rows in HEMPLAYERNOTES are updated.
    If i use the IN keyword only one row is updated.

    It makes no sense to me

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Are there any NULL values in any of the player_id columns?

  7. #7
    Join Date
    Dec 2010
    Posts
    7
    Quote Originally Posted by shammat View Post
    Are there any NULL values in any of the player_id columns?
    Nope, all the rows have a valid player_id

  8. #8
    Join Date
    Dec 2010
    Posts
    7
    Quote Originally Posted by shammat View Post
    Are there any NULL values in any of the player_id columns?
    Nope, all the rows have a valid player_id.

    Ah wait, I might have the problem. I have 15,000 player_id but only 800+ in hemplayernotes. Perhaps the update only updates 1 row as there is only the one match.

    Let me try inserting first for each of the player_id then run the update.

  9. #9
    Join Date
    Dec 2010
    Posts
    7
    bingo (my bad)

    Code:
    INSERT INTO hemplayernotes (player_id) select player_id from filterGiveUp;
    then

    Code:
    UPDATE hemplayernotes 
       SET icon_id=2 
    WHERE player_id in (SELECT f.player_id 
                        FROM filterGiveUp f) returning player_id;
    29 rows updated.

    Thanks for help, I would have been stumbling around a lot longer without you clarifying some of the basics for me.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by simaomacaco View Post
    I have 15,000 player_id but only 800+ in hemplayernotes. Perhaps the update only updates 1 row as there is only the one match.
    That was exactly what my question "Are all 29 player_id's actually in hemplayernotes?" was referring to.

  11. #11
    Join Date
    Dec 2010
    Posts
    7

    streaut trust

    Code:
    --build up the stats
    UPDATE hemplayernotes 
    	SET note = substring(note from 1 for char_length(note)) 
                        || chr(10) || 'Me v (3b) = '|| toImport.player_id
    	FROM toImport
    	WHERE hemplayernotes.player_id in (SELECT f.player_id 
    	FROM toImport f) returning hemplayernotes.player_id,hemplayernotes.note;
    Actually, i am not convinced this worked as intended. It is supposed to update HEMPLAYERNOTES a 'note' field by concatenating the corresponding 'stat' string from of 1150 records matched via 'player_id'

    As it stands, every record is updated with the same 'stat' value.

    Any thoughts on why this is not working.

    --- some more info
    so far i have inserted a record into HEMPLAYERNOTES for every entry in toImport.

    toImport is a view of 'player_id' and 'stat'
    hemplayernotes is a table of 'playernote_id', 'player_id', 'icon_id' and some other irrelvant things

  12. #12
    Join Date
    May 2008
    Posts
    277
    Code:
    update table1
    set column_from_table1 = column_from_table2
    from table2
    where table1.id = table2.id

  13. #13
    Join Date
    Oct 2011
    Posts
    2

    Arrow update multiple rows from subquery

    if you want to update all rows with fixed value of Table1 thats id in a subquery then its simple.

    update t1
    set t1.ColumnName = 2
    from Table2 t1
    where t1.id in (select isnull(t2.id,0) id from Table2)

    here Table2 can be a subquery like

    update t1
    set t1.ColumnName = 2
    from Table2 t1
    where t1.id in (select isnull(t2.id,0) id from (slelect t3.id from Table3 t3,Table4 t4 where t3.id=t4.id ) t2)


    or if want to update all rows of Table1 with the rows value in a Subquery match by id then

    update t1
    set t1.ColumnName = t2.ColumnName
    from Table2 t1, Table2 t2
    where t1.id = t2.id


    here Table2 can be another subquery like

    update t1
    set t1.ColumnName = t2.ColumnName
    from Table2 t1, (select id,ColumnName from Table3) t2
    where t1.id = t2.id

Posting Permissions

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