Mugoma J. Okomba Mugoma J. Okomba - 2 months ago 6
MySQL Question

Executing an update and checking for duplicates at same time in MySQL/Laravel

I am using Laravel 4.2 and have a query:

DB::table('data')->whereIn('t_id', $new_ids);
->whereNotIn('l_id', $old_ids);
->groupBy('l_id')->update(array('t_id' => $new_t_id));


This causes an error:

500 - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1569648-7302' for key 'data_l_id_t_id_unique' (SQL: update `data` set `t_id` = 7302 where `t_id` in (4772, 4860, 4861, 5653, 6396, 6743) and `l_id` not in (2994190)) @ /


The problems seems to be with
groupBy()
clause which is not being executed, thus making duplicate l_id to be pulled into the query:

mysql> select l_id from data where t_id=7302;
+---------+
| l_id |
+---------+
| 2994190 |
+---------+
1 row in set (0.00 sec)


mysql> select l_id from data where t_id in (4772, 4860, 4861, 5653, 6396, 6743);
+---------+
| l_id |
+---------+
| 1569648 |
| 1593870 |
| 1594096 |
| 1628872 |
| 1569648 |
| 1593870 |
| 1594096 |
| 1628872 |
| 1569648 |
| 1593870 |
| 1594096 |
| 1628872 |
| 1879092 |
| 2283518 |
| 2284586 |
| 2604466 |
+---------+
16 rows in set (0.00 sec)



mysql> select l_id from data where t_id in (4772, 4860, 4861, 5653, 6396, 6743) GROUP BY l_id;
+---------+
| l_id |
+---------+
| 1569648 |
| 1593870 |
| 1594096 |
| 1628872 |
| 1879092 |
| 2283518 |
| 2284586 |
| 2604466 |
+---------+
8 rows in set (0.00 sec)


Schema:

mysql> show create table data;

CREATE TABLE `data` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`l_id` bigint(20) unsigned NOT NULL,
`t_id` bigint(20) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_l_id_tag_id_unique` (`l_id`,`t_id`),
KEY `data_t_id_foreign` (`t_id`),
CONSTRAINT `data_l_id_foreign` FOREIGN KEY (`l_id`) REFERENCES `lis` (`id`),
CONSTRAINT `data_t_id_foreign` FOREIGN KEY (`t_id`) REFERENCES `tas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4544794 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


Basically I need to update t_id for certain l_ids but still ensure no duplicate t_id/l_id happen. I could do this by looping through each t_id and checking for duplicates before updating but thought a shortcut via
groupBy()
would be a better way of doing it.

Is it possible to make Laravel do a
groupBy()
while updating? More generally can an update be executed while checking for duplicates, even in plain SQL?

Edit: Separating update from group by

Making UPDATE and GROUP BY seperate helps resolve GROUP BY problem but not duplicate problem:

$required_l_ids = DB::table('data')->whereIn('t_id', $new_ids);
->whereNotIn('l_id', $old_ids);
->groupBy('l_id')->lists('l_id');


if ( !empty($required_l_ids) ) {
DB::table('data')->whereIn('l_id', $required_l_ids)->whereIn('t_id', $new_ids)->update(array('t_id' => $new_tag_id));
}


Still gives an error:

500 - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1593870-7302' for key 'data_l_id_t_id_unique' (SQL: update `data` set `t_id` = 7302 where `l_id` in (1593870, 1594096, 1628872, 1879092, 2283518, 2284586, 2604466) and `t_id` in (4772, 4860, 4861, 5653, 6396, 6743)) @ /


Edit 2: Sample data

CREATE TABLE `data` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`l_id` bigint(20) unsigned NOT NULL,
`t_id` bigint(20) unsigned NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_l_id_t_id_unique` (`l_id`,`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4544794 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT into data (l_id, t_id) VALUES (1569648,7302);
INSERT into data (l_id, t_id) VALUES (2994190,7302);
INSERT into data (l_id, t_id) VALUES (1593870,4772);
INSERT into data (l_id, t_id) VALUES (1594096,4772);
INSERT into data (l_id, t_id) VALUES (1628872,4772);
INSERT into data (l_id, t_id) VALUES (1569648,4860);
INSERT into data (l_id, t_id) VALUES (1593870,4860);
INSERT into data (l_id, t_id) VALUES (1594096,4860);
INSERT into data (l_id, t_id) VALUES (1628872,4860);
INSERT into data (l_id, t_id) VALUES (1569648,4861);
INSERT into data (l_id, t_id) VALUES (1593870,4861);
INSERT into data (l_id, t_id) VALUES (1594096,4861);
INSERT into data (l_id, t_id) VALUES (1628872,4861);
INSERT into data (l_id, t_id) VALUES (1879092,5653);
INSERT into data (l_id, t_id) VALUES (2283518,6396);
INSERT into data (l_id, t_id) VALUES (2284586,6396);
INSERT into data (l_id, t_id) VALUES (2604466,6743);


UPDATE data AS d1 LEFT JOIN data AS d2 ON d1.l_id = d2.l_id AND d2.t_id = 7302 SET d1.t_id = 7302 WHERE d1.t_id IN (4772,4860,4861,5653,6396,6743) AND d1.l_id NOT IN (1569648,2994190) AND d2.l_id IS NULL;


sqlfiddle: http://sqlfiddle.com/#!9/e9a50

Error:
Duplicate entry '1593870-7302' for key 'data_l_id_t_id_unique'

Answer

I don't know the Laravel syntax, but I think this is the MySQL syntax for what you want:

UPDATE data AS d1
JOIN (SELECT l_id, MIN(t_id) AS min_t_id
      FROM data
      WHERE d1.t_id IN ($new_ids)
      AND d1.l_id NOT IN ($old_ids)
      GROUP BY l_id) AS d3 ON d1.l_id = d3.l_id AND d1.t_id = d3.min_t_id
LEFT JOIN data AS d2 ON d1.l_id = d2.l_id AND d2.t_id = $new_tag_id
SET d1.t_id = $new_tag_id
WHERE d2.l_id IS NULL

This combines an UPDATE with the LEFT JOIN/NULL pattern in Return row only if value doesn't exist

The first JOIN makes sure that only one row for each l_id is updated, so you don't create duplicates. It arbitrarily chooses the lowest t_id to replace.

DEMO