Chris Beckett - 1 year ago 39

SQL Question

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

`UPDATE`

I want to be able to achieve changing

`school_id_2`

`school_id_3`

`school_id_4`

`id`

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 Source

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)
```