mysql 按条件join_在mysql中根据条件使用join更新记录

也许这里有比严格要求更多的查询,但是你知道。。。

DROP TABLE IF EXISTS utr_details;

CREATE TABLE utr_details

(id SERIAL PRIMARY KEY

,utr INT NOT NULL

,utr_amt INT NOT NULL

,match_flag TINYINT NOT NULL DEFAULT 0

);

INSERT INTO utr_details VALUES

(1,1,10,0),

(2,1,20,0),

(3,1,20,0),

(4,2,30,0),

(5,2,20,0),

(6,2,10,0);

DROP TABLE IF EXISTS export_data;

CREATE TABLE export_data

(utr INT NOT NULL PRIMARY KEY

,utr_sum_amt INT NOT NULL

);

INSERT INTO export_data VALUES

(1, 50),

(2,100);

UPDATE utr_details a

JOIN

( SELECT x.utr

FROM

( SELECT utr

, SUM(utr_amt) utr_sum_amt

FROM utr_details

GROUP

BY utr

) x

JOIN export_data y

ON y.utr = x.utr

AND y.utr_sum_amt = x.utr_sum_amt

) b

ON b.utr = a.utr

SET match_flag = 1;

Query OK, 3 rows affected (0.05 sec)

SELECT * FROM utr_details;

+----+-----+---------+------------+

| id | utr | utr_amt | match_flag |

+----+-----+---------+------------+

| 1 | 1 | 10 | 1 |

| 2 | 1 | 20 | 1 |

| 3 | 1 | 20 | 1 |

| 4 | 2 | 30 | 0 |

| 5 | 2 | 20 | 0 |

| 6 | 2 | 10 | 0 |

+----+-----+---------+------------+