CODEWITHSUNDEEP
mysqlsql-updatesubquery
Werner
Werner

Reputation: 1847

MySql: Update table with minimum of subset of table

This is my test-database:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
    PRIMARY KEY (`id`),
    `parent` int(10) unsigned DEFAULT NULL, 
    `number` int DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO t1 SET id=1, parent=NULL, number=NULL;
INSERT INTO t1 SET id=2, parent=NULL, number=NULL;
INSERT INTO t1 SET id=3, parent=NULL, number=NULL;

INSERT INTO t1 SET parent=1, number=4;
INSERT INTO t1 SET parent=1, number=2;
INSERT INTO t1 SET parent=1, number=3;

INSERT INTO t1 SET parent=2, number=10;
INSERT INTO t1 SET parent=2, number=20;
INSERT INTO t1 SET parent=2, number=30;

INSERT INTO t1 SET parent=3, number=3;
INSERT INTO t1 SET parent=3, number=2;
INSERT INTO t1 SET parent=3, number=1;

I'm trying to set the "number" of each parent to the minimum of the "number" of its children:

UPDATE t1 
SET number=(SELECT min(number) FROM t1 AS t2 WHERE t1.id=t2.parent) 
WHERE t1.parent IS NULL;

But this gives me an error: ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

I understand the way would be a JOIN, but I don't get it right:

UPDATE t1 
LEFT JOIN
    (SELECT min(t2.number) AS minnumber, t2.parent AS parent FROM t1 AS t2) t2
ON t1.id = t2.parent
SET t1.number = t2.minnumber
WHERE t1.parent IS NULL;

But "number" of the id1, id2, id3 is just NULL after that. I assume thats because inside the JOIN I cant restrict t2 to work only on the actual parent so it works on the whole table. Any idea?

Upvotes: 0

Views: 116

Answers (1)

spencer7593
spencer7593

Reputation: 108420

Looks like you need to add a GROUP BY clause to the inline view query (t2), so that it returns a row for each parent.

Without the GROUP BY clause, that query is returning a single row. (You can run just the t2 query, and see that.)

When we add the GROUP BY clause, we should get all the distinct values of parent returned, along with the minimum number associated with each parent.

SELECT MIN(t2.number) AS minnumber
     , t2.parent AS parent 
  FROM t1 t2
 GROUP BY t2.parent

Upvotes: 2

Related Questions