mysql delete and update with join

20/05/2020

you can start from a select

select *
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

when good, switch to delete mode

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

or to update

update T1
INNER JOIN T2 ON T1.key = T2.key
SET T1.C2 = T2.C2, 
WHERE condition;

or when we need group functions

UPDATE property AS p
INNER JOIN (
    SELECT c.propertyId, count(IF(c.candidate = 0, c.id, null)) as nbProspect, count(IF(c.candidate, c.id, null)) as     nbCandidate
    from candidate as c
    where c.archive = 0
    GROUP BY c.propertyId
) AS x ON p.id = x.propertyId
set p.nbProspect = x.nbProspect,
p.nbCandidate = x.nbCandidate
WHERE p.id = 51

Raccourcis