MySQL query: Updating foreign key with unique Id

Question

I need to link two tables 1-to-1, but the values that are to be compared and linked upon, are not unique.

I cannot find a way. As an example, I added a very simple version.

CREATE TABLE `T1` (
  `id` int(6) unsigned NOT NULL,
  `cmp` int(3) NOT NULL,
  `uniqueT2Id` int(3) unsigned,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `T2` (
  `id` int(6) unsigned NOT NULL,
  `cmp` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `T1` (`id`, `cmp`, `uniqueT2Id`) VALUES
  ('1', '1', NULL),
  ('2', '1', NULL),
  ('3', '2', NULL),
  ('4', '3', NULL),
  ('5', '1', NULL);

INSERT INTO `T2` (`id`, `cmp`) VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '3');

UPDATE T1 SET uniqueT2Id=
(SELECT id FROM T2 WHERE T2.cmp=T1.cmp AND
  id NOT IN (SELECT * FROM 
               (SELECT uniqueT2Id FROM T1 WHERE uniqueT2Id IS NOT NULL) X) 
 ORDER BY id ASC LIMIT 1);

SELECT * FROM T1;

http://sqlfiddle.com/#!9/3bab7c/2/0

The result is

id  cmp uniqueT2Id
1   1   1
2   1   1
3   2   4
4   3   5
5   1   1

I want it to be

id  rev uniqueT2Id
1   1   1
2   1   2
3   2   4
4   3   5
5   1   3

In the UPDATE I try to pick an Id that is not already used, but this obviously does not work. Does anyone know a way to do this in MySQL, preferrably without PHP?

Despite all the information you gave us, I actually don't see a clear problem statement. In particular, what is the join logic for connecting these two tables? — Tim Biegeleisen, Mar 08 '18 at 06:22
The actual table and situation is incredibly complicated, that is why I simplified enormously. The logic is, roughly speaking, that I need a 1-on-1 relation and getting a 1-on-many relation. Or even simpler: I need to UPDATE the column in every record to a unique value (out of a lookup table) but I am not getting a unique value. — Roemer, Mar 08 '18 at 06:57
Or even clearer: the "NOT EXISTS " part of the query doesn't work, how can I fix this? — Roemer, Mar 08 '18 at 07:07
Your question is still unclear. If you are unable to articulate the join logic, then how can you expect us to do it? — Tim Biegeleisen, Mar 08 '18 at 07:31
This could be a block and row number join between the 2 tables with an update — P.Salmon, Mar 08 '18 at 07:47
P.Salmon: Do you mean like in https://stackoverflow.com/questions/19064881/is-there-a-simple-way-join-rows-by-their-row-number-in-mysql? Or could you in any other way send me into the right way? Googling for 10 mins now and not finding much clues... — Roemer, Mar 08 '18 at 07:55
Tim: but I don't want you to articulate a join logic, it is not about the join logic. It is about getting unique values in an UPDATE query. That's all I need. I know that is a situation one ine general doesn't want to be in, but real life practice is a b*tch, sometimes, and in my case it most certainly is. — Roemer, Mar 08 '18 at 07:58

score 0 · Accepted Answer · answered Mar 08 '18 at 11:05

I found an answer myself, with variables. It is horrible and requires a dummy field in the table, but it works. I am open for improvements.

CREATE TABLE `T1` (
  `id` int(6) unsigned NOT NULL,
  `cmp` int(3) NOT NULL,
  `uniqueT2Id` int(3) NULL,
  `dummy` varchar(200) NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `T2` (
  `id` int(6) unsigned NOT NULL,
  `cmp` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `T1` (`id`, `cmp`, `uniqueT2Id`) VALUES
  ('1', '1', NULL),
  ('2', '1', NULL),
  ('3', '2', NULL),
  ('4', '5', NULL),
  ('5', '3', NULL),
  ('6', '1', NULL);

INSERT INTO `T2` (`id`, `cmp`) VALUES
  ('1', '1'),
  ('2', '1'),
  ('3', '1'),
  ('4', '2'),
  ('5', '3');

SET @taken = '/' ;
UPDATE T1 
SET uniqueT2Id= @new:=
  (SELECT id FROM T2 WHERE T2.cmp=T1.cmp AND
    INSTR(@taken, CONCAT('/',id,'/')) = 0
   ORDER BY id ASC LIMIT 1),
  dummy=IF(@new IS NOT NULL,@taken:=CONCAT(@taken, @new, "/"),NULL);

http://sqlfiddle.com/#!9/4a61d/1/0

Not to mention it is horribly slow. – Roemer Mar 08 '18 at 12:06 — Roemer, Mar 08 '18 at 12:06

MySQL query: Updating foreign key with unique Id

1 Answers1