Chris Beckett Chris Beckett - 5 months ago 10
SQL Question

Can UPDATE be written in different way?

I've been trying and trying to work out how I can make this

UPDATE
query simpler and it's got to the point where I can't figure it out.

I want to be able to achieve changing
school_id_2
or
school_id_3
or
school_id_4
to zero if they equal the
id
in this case is 25. I want to be able to do this without running all the separate updates.

Is it possible to do this in one single
UPDATE
?

Query:

$query = "
UPDATE pupil_blog_quad_overview,
pupil_blog_quad
SET pupil_blog_quad_overview.accepted = '0',
pupil_blog_quad_overview.school_id = '0',
pupil_blog_quad_overview.updated = NOW()
WHERE pupil_blog_quad_overview.school_id = '25' AND
pupil_blog_quad_overview.group_id = '58';

UPDATE pupil_blog_quad_overview,
pupil_blog_quad
SET pupil_blog_quad.school_id_2 = '0',
pupil_blog_quad.updated = NOW()
WHERE pupil_blog_quad.school_id_2 = '25' AND
pupil_blog_quad_overview.group_id = '58';

UPDATE pupil_blog_quad_overview,
pupil_blog_quad
SET pupil_blog_quad.school_id_3 = '0',
pupil_blog_quad.updated = NOW()
WHERE pupil_blog_quad.school_id_3 = '25' AND
pupil_blog_quad_overview.group_id = '58';

UPDATE pupil_blog_quad_overview,
pupil_blog_quad
SET pupil_blog_quad.school_id_4 = '0',
pupil_blog_quad.updated = NOW()
WHERE pupil_blog_quad.school_id_4 = '25' AND
pupil_blog_quad_overview.group_id = '58'";

Answer

Yes, you can do it with two updates , not one since your are updating two different tables.

The 3 updates can be done with one query using CASE EXPRESSION:

UPDATE pupil_blog_quad_overview, pupil_blog_quad
   SET pupil_blog_quad_overview.accepted  = '0',
       pupil_blog_quad_overview.school_id = '0',
       pupil_blog_quad_overview.updated   = NOW()
 WHERE pupil_blog_quad_overview.school_id = '25'
   AND pupil_blog_quad_overview.group_id = '58';


UPDATE pupil_blog_quad_overview, pupil_blog_quad
   SET  pupil_blog_quad.updated = NOW()
        ,pupil_blog_quad.school_id_2 = CASE WHEN pupil_blog_quad.school_id_2 = '25' THEN '0' ELSE pupil_blog_quad.school_id_2 END
        ,pupil_blog_quad.school_id_3 = CASE WHEN pupil_blog_quad.school_id_3 = '25' THEN '0' ELSE pupil_blog_quad.school_id_3 END
        ,pupil_blog_quad.school_id_4 = CASE WHEN pupil_blog_quad.school_id_4 = '25' THEN '0' ELSE pupil_blog_quad.school_id_4 END 
 WHERE pupil_blog_quad_overview.group_id = '58'
   AND '25' in(pupil_blog_quad.school_id_2,pupil_blog_quad.school_id_3,pupil_blog_quad.school_id_4)