How to substitute a value depending on the presence of a record in another MySQL table?

Question

MySQL

Good day
There is a table Pricelist:

and a table Lots:

Task: if for a row from the table Pricelist , where id = 2 (id is substituted by PHP), there is a row in the table Lots , where the fields Pricelist.ID = Lots.ID_of_price AND Pricelist.prices = Lots.prices , then update Own_Price = 1 (for the row where id = 2), otherwise update Own_Price = 0 (for the row where id = 2)
Is it possible to do this somehow using only one MySQL query (request is sent from PHP) ?

Answer 1

You can, use the IF statement , or CASE
Something like this:

UPDATE Pricelist SET Own_Price = if (EXISTS(SELECT `id` FROM `Lots` WHERE `Pricelist`.`ID` = `Lots.`ID_of_price` AND `Pricelist`.`prices` = `Lots`.`prices` LIMIT 1),  1, 0) WHERE `id` = 2