5

Conceptual doubt:

I've tried many things (MySQL) but the only way this update is working is the way I described here. It is working, but I want to make sure about this:

when we use join in an update, and the value I will set is from the table that is being joined, and there are more than one join possibilities for each row, will the update get always the first possible line to make the join?

create table letter (id_letter bigint, id_group_table bigint, letter char(1));  
create table group_table (id_group_table bigint, id_whatever bigint, champion char(1));
create table whatever (id_whatever bigint);

insert into whatever values (1);
insert into whatever values (2);
insert into whatever values (3);
insert into whatever values (4);
insert into whatever values (5);

insert into group_table values(1, 1, null); -- champion should be B
insert into group_table values(2, 2, null); -- champion should be C
insert into group_table values(3, 3, null); -- champion should be X
insert into group_table values(4, 4, null); -- champion should be C
insert into group_table values(5, 5, null);

insert into letter values(1,1,'A');
insert into letter values(2,1,'B');
insert into letter values(3,1,'B');

insert into letter values(4,2,'B');
insert into letter values(5,2,'C');
insert into letter values(6,2,'C');

insert into letter values(7,3,'X');
insert into letter values(8,3,'X');
insert into letter values(9,3,'Y');

insert into letter values(10,4,'A');
insert into letter values(11,4,'A');
insert into letter values(12,4,'C');

insert into letter values(13,4,'C');
insert into letter values(14,4,'C');
insert into letter values(15,4,'C');

insert into letter values(16,5,'B');
insert into letter values(17,5,'C');
insert into letter values(18,5,'C');

-- update to set champions for everybody

update group_table 
join
  (select  letter,
  id_group_table, 
  count(letter) as occurences
  from letter
  group by id_group_table, letter 
  order by occurences desc
  ) tab on group_table.id_group_table = tab.id_group_table

set champion = tab.letter
where group_table.id_whatever in (1,2,3,4);

http://sqlfiddle.com/#!2/6ea061/1

Thanks!!

qxlab
  • 1,506
  • 4
  • 20
  • 48

1 Answers1

2

The documentation says:

Each matching row is updated once, even if it matches the conditions multiple times.

However, it doesn't specifically say that it's the first value that's always used, so I'm not sure if you can reliably depend on this. It would be better if you wrote the subquery so that it only returns the champion for each id_group_table. There are numerous SO questions that show how to write such a query.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks for the answer, but it is not that easy, at least for me. In MySQL you cannot make a reference to the `update` table. If this was possible, I would just link the sub query to the `update` table, group by letter, and limit the result by 1. – qxlab Jul 31 '14 at 03:27
  • 1
    You don't need to make a reference to the update table. You just need to rewrite the subquery so it only returns one row per `id_group_table`. See all the questions tagged `[mysql] [greatest-n-per-group]`. – Barmar Jul 31 '14 at 03:28
  • If you want to check the order that it is updated, create a table like group_table_chk and create a trigger on group_table after update, inside it you add an insert command to the chk table. – Jorge Campos Jul 31 '14 at 03:31
  • Hm thanks, this subject is new for me! I'll understand and accept your answer! – qxlab Jul 31 '14 at 03:38
  • 2
    Here's the question I usually refer to for getting the last row of a group: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group – Barmar Jul 31 '14 at 03:39