The best way to write this query

Question

I have two MySql tables as shown below:

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 A.status can be updated if all B.status match when A.id = B.tableA_id using a single request ?

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

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

thank

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)

(: , COALESCE(..., A.status) NULL, B

,

Answer 2

MySql, MSSQL - :

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

MySQL, , . , .

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 updated the query and now it gives 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');