change status when exceeding the date column

Question

change status when exceeding the date column

Navigation

#1 by (1 votes)

1

Good morning, I have the following problem: I need to change the states after a deadline (table date column date ) and at the same time change the states where reference the iddocumento (including the one in the document ),

This is my table document :

This is my table date

This is my table movement

With this code I can change the status of the date

table

SET GLOBAL event_scheduler = ON;
SELECT @@event_scheduler;
CREATE EVENT verificar_tramite
ON SCHEDULE
EVERY 1 MINUTE
DO
UPDATE fecha f
set f.estado='INACTIVO' 
where f.fecha <= NOW();

But I do not know how I could get the iddocumento and apply it in the other tables, any suggestions?

mysql

asked by Raphael 02.12.2017 в 20:10

source

1 answer

Shared memory in Ubuntu c ++ fragment on android

score 1 · Accepted Answer

I did not know what could be done, but it seems that MySQL is able to update the values of more than one table at a time using a single statement UPDATE . Try the following statement UPDATE :

update fecha f
  left join documento d
    on d.id = f.iddocumento
  left join movimiento m
    on m.iddocumento = f.iddocumento
  set f.estado = 'INACTIVO',
      d.estado = 'INACTIVO',
      m.estado = 'INACTIVO'
 where f.fecha <= now()
   and f.estado = 'ACTIVO'

And note that I added an additional condition f.estado = 'ACTIVO' to avoid updating the records that have already been marked INACTIVO .

(A small caveat: The fact that you have to modify the same column estado in 3 tables at once probably indicates that your model is not well normalized.) If you can still make corrections, it might be better if the column estado defines it in only one of the tables.)