CODEWITHSUNDEEP
mysqlsqlsql-update
Alistair Boyer
Alistair Boyer

Reputation: 11

MYSQL update from another table with multiple entries

I have seen a bunch of helpful answers about updating table values from a different table with multiple values based on a timestamp using a MAX() subquery.

e.g. Update another table based on latest record

I was wondering how this compares with doing an ALTER first and relying on the order in the table to simplify the UPDATE. Something like this:

ALTER TABLE `table_with_multiple_data` ORDER BY `timestamp` DESC;

UPDATE `table_with_single_data` as `t1` 
  LEFT JOIN `table_with_multiple_data` AS `t2` 
  ON `t1`.`id`=`t2`.`t1id` 
  SET `t1`.`value` = `t2`.`value`;

(Apologies for the pseudocode but I hope you get what I'm asking)

Both achieve the same for me but don't really have a big enough data set to see any difference in speed.

Thanks!!

Upvotes: 0

Views: 200

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You would normally use a correlated subquery:

UPDATE table_with_single_data t1
  SET t1.value = (select t2.value
                  from table_with_multiple_data t2
                  where t2.t1id = t1.id 
                  order by t2.timestamp desc
                  limit 1
                 );

If your method happens to work, that is just happenstance. Even if MySQL respected the ordering of tables, such ordering would not survive the join operation. Not to mention the fact that there is no guarantee on *which * value is assigned when there is multiple matching rows.

Upvotes: 1

Related Questions