CODEWITHSUNDEEP
mysqlsql
Alejandro Muñoz Schumacher
Alejandro Muñoz Schumacher

Reputation: 39

Update record comparing different columns

What I'm trying is to generate a SQL statement that finds me in the rows that have repeated data, and at the same time in different columns.

My table is something like this:

ID  DATE        ONE  TWO  TRES status
-- ----------   ---  ---  ---  -----
1  2018-02-18   21   22   23   B
2  2018-02-18   01   21   44   B
3  2018-02-18   55   66   77   B

What I have to do first is find the records that have the same date ... I have no problem.

The problem is when I try to compare columns ONE TWO and TRES to see if it has a value in common.

It is logical that I will have to go comparing from ONE to TWO, ONE to THREE and TWO to THREE.

In the example, row 1 and 2 have the same date and Column ONE has the same value as DOS and therefore it would have to update its status to E (error) ..

'UPDATE `table` SET `status` = "E" WHERE `DATE` = `DATE`'

Thank...

Upvotes: 1

Views: 30

Answers (1)

John Woo
John Woo

Reputation: 263813

This query below will give you your desired result:

UPDATE  TableName a
        INNER JOIN
        (
            SELECT  a.ID, b.ID AS ID2
            FROM    TableName a
                    INNER JOIN TableName b
                        ON a.Date = b.Date
                            AND
                            (
                                a.One = b.Two
                                OR a.One = b.Tres
                                OR a.Two = b.Tres
                            )
        ) b ON a.ID = b.ID OR a.ID = b.ID2
SET     Status = 'E'

basically, what it does is it joins that table to a subquery which conditions you mentioned above to get the invalid rows and the resulting rows will be join again to itself and update its status to E.

Here's a Demo.

Upvotes: 1

Related Questions