UPDATE with INNER JOIN in Mysql

Question

UPDATE with INNER JOIN in Mysql

Navigation

#1 by (0 votes)

0

I have this database schema for an electronic calendar.

I have a table of events and one of equipment which is a many to many relationship. Once an event is deleted, I need to change the status of the equipment (eg, projector, notebook, pointer) related to that event, which are stored in the evt_equip table.

My idea is to do this with a Trigger, so that once an event is eliminated, the trigger is triggered and the status of the equipment related to the event is changed (from 1 to 0).

I think the right way is to do an UPDATE with JOIN but there's the problem. I passed it trying and I do not get the result.

The code I was testing is this:

delimiter //
create trigger evento_AD after delete on evento for each row
begin
declare id int;
set id=old.idevento;
insert into pruebas(valor) value(id);
update equipamiento e inner join evt_equip ee on e.idequipamiento = ee.idequip set estado=0 where ee.idevento=id;
end//

mysql sql transacciones trigger

asked by Gonzalo GM 13.12.2016 в 14:49

source

1 answer

Detect item already accessed in Array How to receive and display the data I generated from a json in a php file?

score 0 · Accepted Answer

evt_equip is a weak entity that requires both the event table and equipment to identify itself. So if you try to delete an event that is referenced in evt_equip should give you error.

If your purpose is to change the state of the equipment so that they are free when an event is finished, I recommend that instead of deleting the event, update the team that is occupied by expired events:

UPDATE equipamiento 
SET estado = 0 
INNER JOIN evt_equip ON (idequipamiento = idequip) 
INNER JOIN evento ON (evento.idevento = evt_equip.idevento) 
WHERE evento.fecha_fin < sysdate