Filter in update of a table based on information from another table

Question

Filter in update of a table based on information from another table

Navigation

#1 by (1 votes)

0

Hello, I need to make a UPDATE in a table (in the example table 'reservations'), based on information from another table (in the example the table 'administration'). Good until then I could do with the help of a person here of the forum ...

Example of what I already have: (perform the update in the 'reservations' table if there is no duplication of the same record in the 'administration' table).

UPDATES reservas t1 SET t1.status_reserva = 'Entregue' WHERE NOT EXISTS (SELECT * FROM administracao t2 WHERE t2.cod = t1.cod1 )

Now I need to do this, only by putting a filter so that UPDATE executes only if the 'status_reserve' field is different from the word 'Canceled'.

I tried to do this, but it did not work:

UPDATES reservas t1 SET t1.status_reserva = 'Entregue' WHERE NOT EXISTS (SELECT * FROM administracao t2 WHERE t2.cod = t1.cod1 ) AND status_reserva != 'Cancelado'

Please someone who has a solution to this problem, or can not do this?!

php mysql

asked by anonymous 03.12.2018 / 21:54

1 answer

InnerJoin with 3 tables Mysql [closed] Form in loop in laravel

score 1 · Accepted Answer

You could just be doing LEFT JOIN and checking to see if it's NULL at the end ... I'd get something +/- this way:

UPDATE reservas t1
LEFT JOIN administracao t2
ON t2.cod = t1.cod1
SET t1.status_reserva = 'Entregue'
WHERE t2.cod IS NULL && t1.status_reserva != 'Cancelado';

Or even pass the condition from status_reserva to before EXISTS :

UPDATES reservas t1 SET t1.status_reserva = 'Entregue' WHERE t1.status_reserva != 'Cancelado' AND NOT EXISTS (SELECT * FROM administracao t2 WHERE t2.cod = t1.cod1 )

See if any of them fit what you want to do in your code ...:)