CODEWITHSUNDEEP
mysql
Francis
Francis

Reputation: 147

update records with join on a condition in mysql

I have 2 tables 

utr_details

utr | utr_amt |match_flag(default 0)
1   |10       |0
1   |20       |0
1   |20       |0
2   |30       |0
2   |20       |0
2   |10       |0

export_data
utr | utr_sum_amt
1   | 50
2   | 100

As you can see above the sum of utr_amt in the table utr_details of utr 1 is 50 which is equal to the utr_sum_amt in export export_data against utr 1. But the sum of utr 2 in both tables is not equal. My task is to update the value of match_flag to 1 where the sum is equal in both tables.In this case match_flag will be 1 for utr 1 in table utr_details. Please help me generating a mysql query.Is is possible to achieve this in a single query?

Upvotes: 0

Views: 44

Answers (3)

slowko
slowko

Reputation: 875

UPDATE utr_details 
   JOIN (SELECT aggregates.utr 
         FROM   (SELECT utr, Sum(utr_amt) utr_total 
                 FROM   utr_details 
                 GROUP  BY utr) aggregates 
                JOIN export_data 
                  ON aggregates.utr = export_data.utr 
         WHERE  utr_total = utr_sum_amt) aggregates_equal 
     ON aggregates_equal.utr = utr_details.utr 
SET match_flag = 1;

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Perhaps there's more query here than is strictly necessary, but you get the idea...

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 |
+----+-----+---------+------------+

Upvotes: 1

Ildar Akhmetov
Ildar Akhmetov

Reputation: 1431

The query could look like this:

UPDATE utr_details 
SET match_flag = 1
WHERE utr IN (
SELECT utr, utr_sum_amt, auto_sum.sum.correct
FROM export_data 
INNER JOIN 
(SELECT utr, SUM(utr_amt) AS sum_correct
FROM utr_details
GROUP BY utr) auto_sum
WHERE utr_sum_amt = auto_sum.sum.correct
)

Here's how it works.

1) Calculate the actuals sums:

SELECT utr, SUM(utr_amt) AS sum_correct
FROM utr_details
GROUP BY utr

2) Join export_data with this new select and add a WHERE clause to select just the rows where the sums are different.

3) Run UPDATE using the above results as a filter.

Upvotes: 0

Related Questions