也许这里有比严格要求更多的查询,但是你知道。。。
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 |
+----+-----+---------+------------+