UPDATE in MySql based on the last date of a column

Question

UPDATE in MySql based on the last date of a column

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I am trying to update a table in MySql (Client table status field) based on the last date that this client was visited (field dateTable of the Schedule table), passing as a parameter the number of days that will define the change of the status of the same field (days field of the Customer table).

But the same customer has several visits, and I would need to check the most recent one, and based on this last visit, count the number of days the client is not visited.

I can get through SELECT all the clients and their most up-to-date dates, but I can not update them since I can not compare the most recent date (A.dataAgendada) with a list of dates.

I have tried to use LIMIT 1, but it returns only the first element of the entire query, not the first element (most recent date) of each client.

Could anyone help me?

UPDATE Cliente C INNER JOIN Agendamento A ON C.idCliente = A.idCliente
SET C.status = 'Alerta'
WHERE DATEDIFF(now(), A.dataAgendada) >= C.diasAtencao

AND A.dataAgendada = (
 SELECT A.dataAgendada FROM(
 SELECT MAX(Ag.dataAgendada) as agg FROM CLIENTE Cli INNER JOIN AGENDAMENTO Ag ON Cli.IDCLIENTE = Ag.IDCLIENTE
 GROUP BY Cli.idCliente
 ORDER BY Ag.dataAgendada DESC

 ) as Consulta
);

mysql

asked by anonymous 17.10.2016 / 21:55

2 answers

Auto select option from a select from an input HTTP Error 500 when putting a laravel5.1 project in production

score 1 · Answer 1

Sorry for the delay in returning. Kurole, your answer helped me find the solution to my problem, thank you.

Follow the solution below:

UPDATE Cliente C SET C.status = 'Alerta' WHERE (SELECT (DATEDIFF(now(), MAX(A.dataAgendada))) FROM Agendamento A WHERE A.idCliente = C.idCliente) > C.diasAtencao");

score 1 · Answer 2

Hello, you tried to do this

UPDATE 
   Cliente C 
SET 
   C.status = 'Alerta'
WHERE
  EXISTS(
         SELECT 
            A.dataAgendada
         FROM 
           Agendamento A 
         WHERE 
           C.idCliente = A.idCliente
           AND DATE(A.dataAgendada) = DATE(NOW())
  )