UPDATE in Mysql based on the last date of a column

Asked

Viewed 465 times

2

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

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

I can get through SELECT all clients and their dates more updated, but I can’t update them, because I can’t compare the most recent date (A.datagendada) with a list of dates.

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

Could someone 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

2 answers

1

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

Follows 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");

1

Hello tried to do so

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())
  )

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.