更新可以用不同的方式编写吗？
更新可以用不同的方式编写吗？
提问于 2016-06-16 11:31:02
我一直在尝试并试图弄清楚如何使这个UPDATE查询变得更简单，而且它已经到了我无法理解的地步。
我希望能够实现将school_id_2、school_id_3或school_id_4更改为零，如果它们等于在本例中为25的id。我希望能够做到这一点，而不运行所有单独的更新。
在一个UPDATE中可以做到这一点吗？
查询：
$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'";回答 1
Stack Overflow用户
回答已采纳
发布于 2016-06-16 11:40:51
是的，您可以通过两个更新来完成这个任务，而不是一个，因为您正在更新两个不同的表。
使用CASE EXPRESSION可以使用一个查询完成这3个更新。
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) 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37857916
复制相关文章
相似问题
腾讯云开发者
