Best way to write this query.

Question

I have two MySql tables as shown below with the data shown:

CREATE TABLE `A` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`another_field` varchar(50) DEFAULT NULL
)

INSERT INTO `A` VALUES ('1', null, 'a');
INSERT INTO `A` VALUES ('2', null, 'b');
INSERT INTO `A` VALUES ('3', null, 'c');


CREATE TABLE `B` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`tableA_id` int(12) DEFAULT NULL,
PRIMARY KEY (`id`)
)

INSERT INTO `B` VALUES ('1', 'aa', '1');
INSERT INTO `B` VALUES ('2', 'aa', '1');
INSERT INTO `B` VALUES ('3', 'aa', '2');
INSERT INTO `B` VALUES ('4', 'aa', '3');
INSERT INTO `B` VALUES ('5', 'bb', '3');

I want to know if it is possible to update A.status if all B.status are the same when A.id = B.tableA_id using a single query?

This is what I want my table A to look like:

('1', 'aa', 'a') - Status is updated to 'aa' as B.id 1 & 2 have the same status and same B.tableA_id value.
('2', 'aa', 'b') - Status is updated to 'aa' as B.id 3 has the same status.
('3', null, 'c') - This is not updated because B.id 4 & 5 have different status and the same table2.table1_id value.

Thanks

Answer 1

UPDATE A
SET    status = COALESCE((
           SELECT MAX(B.status)
           FROM   B
           WHERE  B.tableA_id = A.id
           HAVING MAX(B.status) = MIN(B.status)
       ), A.status)

(Note: I added a correction, you need the COALESCE(..., A.status) or otherwise the status will be set to NULL in case there were multiple statuses in B

Answer 2

Not sure about MySql but in MSSQL you could write something like:

UPDATE A SET A.Status = 'aa' FROM A INNER JOIN B on A.id = B.tableA_id WHERE b.status = 'aa'

It should be similar in MySQL, but I'm not if the language supports joins on update. But still I hope it helps.

Answer 3

UPDATE a SET status = 
    (
        SELECT status FROM b WHERE tableA_id = a.id LIMIT 0,1
    )
WHERE id IN
    (
        SELECT tableA_id FROM b
        GROUP BY tableA_id
        HAVING COUNT(DISTINCT status) = 1
    )

Update: Roland was right; I have updated the query and it now yields the correct results.

Answer 4

CREATE TABLE `A` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`another_field` varchar(50) DEFAULT NULL
)

INSERT INTO `A` VALUES ('1', null, 'a');
INSERT INTO `A` VALUES ('2', null, 'b');
INSERT INTO `A` VALUES ('3', null, 'c');


CREATE TABLE `B` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`status` varchar(50) DEFAULT NULL,
`tableA_id` int(12) DEFAULT NULL,
PRIMARY KEY (`id`)
)

INSERT INTO `B` VALUES ('1', 'aa', '1');
INSERT INTO `B` VALUES ('2', 'aa', '1');
INSERT INTO `B` VALUES ('3', 'aa', '2');
INSERT INTO `B` VALUES ('4', 'aa', '3');
INSERT INTO `B` VALUES ('5', 'bb', '3');