Update filter of a table based on information from another table

Asked

Viewed 88 times

0

Hi, I need to make one UPDATE in a table (in the example the table 'reservations'), based on information from another table (in the example the table 'administration'). Good so far I managed to do with the help of a person here from the forum...

Example of what I already have: (update 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 the UPDATE execute only if the'status_reserve 'field is different from the 'Cancelled' word'.

I tried to do it this way, but it didn’t 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 anyone who has a solution to this problem, or if it is not possible to do so?!

php mysql

  • The possible problem would be that you are not indicating which table to take this column from status_reserva? That in the case would be as follows t1.status_reserva

  • :( It didn’t work. This way it doesn’t run Update, it doesn’t recognize if the field status_reserva is or is not filled in with the word Cancelado, there would be another solution?

  • I posted two solutions which I need to test in your code, because I’m not in the mood to test at the moment! Haha

1 answer

1


You could just be doing a LEFT JOIN and checking if he is NULL in the end... It would be something +/- that 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 of status_reserva for before the 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... :)

  • I have succeeded with the second solution presented, thank you very much!!!

Browser other questions tagged php mysql

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