CODEWITHSUNDEEP
mysqldatabasesql-update
Offer
Offer

Reputation: 630

Compare different rows and bring out result

I have a table which requires me to pair certain rows together using a unique value that both the rows share.

For instance in the below table;

+--------+----------+-----------+-----------+----------------+-------------+
| id     | type     |  member   |  code     | description    | matching    |
+--------+----------+-----------+-----------+----------------+-------------+
| 1000   |transfer  |  552123   | SC120314  |  From Gold     |             |
| 1001   |transfer  |  552123   | SC120314  |  To Platinum   |             |
| 1002   |transfer  |  833612   | SC120314  |  From silver   |             |
| 1003   |transfer  |  833612   | SC120314  |  To basic      |             |
| 1004   |transfer  |  457114   | SC150314  |  From Platinum |             |
| 1005   |transfer  |  457114   | SC150314  |  To silver     |             |
| 1006   |transfer  |  933276   | SC180314  |  From Gold     |             |
| 1007   |transfer  |  933276   | SC180314  |  From To basic |             |
+--------+----------+-----------+-----------+----------------+-------------+

basically What i need the query / routine to do is find the rows where the value in the 'member' column for each row match. Then see if the values in the 'code' column for the same found rows also match.

If both columns for both rows match, then assign a value to the 'matching' column for both rows. This value should be the same for both rows and unique to only them.

The unique code can be absolutely anything, so long as it's exclusive to matching rows. Is there any query / routine capable of carrying this out?

Upvotes: 1

Views: 48

Answers (2)

Cyclonecode
Cyclonecode

Reputation: 30071

I'm not sure I understand the question correctly, but if you like to pick out and update rows where the code and member columns matches and set matching to some unique value for each of the related rows, I believe this would work:

UPDATE <table> A 
  INNER JOIN (SELECT * FROM <table>) B ON 
    B.member = A.member && B.code = A.code && A.id <> B.id
SET A.matching = (A.id + B.id);

The matching value will be set to the sum of the id columns for both rows. Notice that updating the matching field this way will not work if there are more than two rows that can match.

Running the above query against your example table would yield:

+------+----------+--------+----------+---------------+----------+
| id   | type     | member | code     | description   | matching |
+------+----------+--------+----------+---------------+----------+
| 1000 | transfer | 552123 | SC120314 | From Gold     | 2001     |
| 1001 | transfer | 552123 | SC120314 | To Platinum   | 2001     |
| 1002 | transfer | 833612 | SC120314 | From Silver   | 2005     |
| 1003 | transfer | 833612 | SC120314 | To basic      | 2005     |
| 1004 | transfer | 457114 | SC150314 | From Platinum | 2009     |
| 1005 | transfer | 457114 | SC150314 | To silver     | 2009     |
| 1006 | transfer | 933276 | SC180314 | From Gold     | 2013     |
| 1007 | transfer | 933276 | SC180314 | From To basic | 2013     |
+------+----------+--------+----------+---------------+----------+

Upvotes: 1

Tam&#225;s Szab&#243;
Tam&#225;s Szab&#243;

Reputation: 348

I can give you a simple query what can do what you need.

tst is the name of the table.

SELECT *, COUNT( t2.id ) as matching FROM tst t LEFT JOIN tst t2 ON t2.member = t.member GROUP BY t.id

Upvotes: 0

Related Questions