Update record comparing different columns

Question

Update record comparing different columns

Navigation

#1 by (2 votes)

2

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

My table is something like this:

ID  FECHA       UNO  DOS  TRES ESTADO
-- ----------   ---  ---  ---  -----
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 UNO DOS and THREE 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 'estado' = "E" WHERE 'fecha' = 'fecha''

Thank you ...

mysql sql query

asked by Alejandro Muñoz Schumacher 19.02.2018 в 03:57

source

1 answer

The text comes out of the tag in html-css Error sending mail with PHPMailer

score 2 · Accepted Answer

If I did not misunderstand your question, the solution would be the following, first try SELECT to see if it shows you the repeated data you want:

SELECT t.* FROM tabla AS t WHERE fecha = '2018-02-18' AND
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t1 WHERE t1.'fecha' = '2018-02-18' AND t.UNO = t1.DOS OR t.UNO = t1.TRES) OR
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t2 WHERE t2.'fecha' = '2018-02-18' AND t.DOS = t2.UNO OR t.DOS = t2.TRES) OR
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t3 WHERE t3.'fecha' = '2018-02-18' AND t.TRES = t3.UNO OR t.TRES = t3.DOS)

Then if it is correct with UPDATE it would be something like this

UPDATE tabla AS t SET estado = "E" WHERE fecha = '2018-02-18' AND
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t1 WHERE t1.'fecha' = '2018-02-18' AND t.UNO = t1.DOS OR t.UNO = t1.TRES) OR
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t2 WHERE t2.'fecha' = '2018-02-18' AND t.DOS = t2.UNO OR t.DOS = t2.TRES) OR
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t3 WHERE t3.'fecha' = '2018-02-18' AND t.TRES = t3.UNO OR t.TRES = t3.DOS)

A small explanation of what you do with UPDATE:
The first row, that is:

UPDATE tabla AS t SET estado = "E" WHERE fecha = '2018-02-18' AND

You are saying that the status will be updated to "E" where the date is the one we are looking for, as long as it complies with the rest of the code, that's why the second line:

EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t1 WHERE t1.'fecha' = '2018-02-18' AND t.UNO = t1.DOS OR t.UNO = t1.TRES) OR

It looks for us if a data of the column ONE is repeated with the data of the DOS or THREE and thus the following two lines of code:

EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t2 WHERE t2.'fecha' = '2018-02-18' AND t.DOS = t2.UNO OR t.DOS = t2.TRES) OR
EXISTS(SELECT * FROM (SELECT * FROM tabla) AS t3 WHERE t3.'fecha' = '2018-02-18' AND t.TRES = t3.UNO OR t.TRES = t3.DOS)

They search if TWO or THREE are repeated in a column. The OR comparator is used since in any of the three cases the status will be changed to "E"