0
table_a

user_id  score  
1        10     
2        10      
3        10      
5        43      
6        43      
9        20      
10       42    



table_b

user_id  flag
1        0
2        0
3        0
4        1
5        1
6        0 
7        1

In the above case, how can I populate the table_b.flag to 1 if user_id from table_a has a (score > 40)?

Nick
  • 138,499
  • 22
  • 57
  • 95
  • user_id at table_a & table_b are related ??? – Aditya Rewari Mar 31 '20 at 06:05
  • 1
    Yes, check INSERT INTO SELECT ... https://dev.mysql.com/doc/refman/8.0/en/insert-select.html , or UPDATE JOIN.. https://stackoverflow.com/questions/36955718/mysql-update-with-select-from-another-table etc – Mate Mar 31 '20 at 06:10
  • 1
    What's your problem? – P.Salmon Mar 31 '20 at 06:32
  • I just want find a faster method than Insert into select, perhaps the update join by mate would do the trick –  Mar 31 '20 at 06:51
  • 1
    Note that you wouldn't normally store derived data – Strawberry Mar 31 '20 at 06:53
  • Inserts add new records to a table updates amend existing records they are not interchangeable. Inserts may be slow because indexes have to be maintained and updates may be slow because they aren't appropriate - either way there is not enough information here to provide you with an answer. You should also look at the explain plan for hints. – P.Salmon Mar 31 '20 at 07:05
  • "Faster" seems to be a confusing word. Do you mean to insert and update the table_b.flag column directly using one query without having to do insert then update or you just want a faster insert like 1000 rows in 1 second? – FanoFN Mar 31 '20 at 07:09

1 Answers1

0

You can use a multi-table UPDATE to get the results you want:

UPDATE table_b b
JOIN table_a a ON a.user_id = b.user_id AND a.score > 40
SET b.flag = 1

The JOIN condition means that only rows in table_b where the corresponding user_id in table_a has a score > 40 will have their flag set to 1. If you want to also set flags to 0 if the corresponding score <= 40, you can use the condition as the value to SET (since MySQL treats booleans as 1 or 0 in a numeric context):

UPDATE table_b b
JOIN table_a a ON a.user_id = b.user_id
SET b.flag = a.score > 40

For your sample data, the result in table_b is the same:

user_id     flag
1           0
2           0
3           0
4           1
5           1
6           1
7           1

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95