CODEWITHSUNDEEP
mysqlsqlsql-update
YaBCK
YaBCK

Reputation: 3029

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'";

Upvotes: 3

Views: 70

Answers (1)

sagi
sagi

Reputation: 40481

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)

Upvotes: 4

Related Questions