问题描述
我有2个表:tbl_taxclasses,tbl_taxclasses_regions
I have 2 tables: tbl_taxclasses, tbl_taxclasses_regions
这是一对多关系,其中主记录ID为classid
.我在第一张表中有一列称为regionscount
This is a one to many relationship, where the main record ID is classid
.I have a column inside the first table called regionscount
因此,我在表1中创建了一个Tax Class.然后在表2中添加了地区/州,为每个地区分配了classid.
So, I create a Tax Class, in table 1. Then I add regions/states in table 2, assigning the classid to each region.
我执行SELECT语句以计数具有相同classid的区域,然后在具有该编号的tbl_taxclasses
上执行UPDATE语句.我更新了regionscount
列.
I perform a SELECT statement to count the regions with that same classid, and then I perform an UPDATE statement on tbl_taxclasses
with that number. I update the regionscount
column.
这意味着我正在编写2个查询.很好,但是我想知道是否有一种方法可以在UPDATE语句内执行SELECT语句,如下所示:
This means I'm writing 2 queries. Which is fine, but I was wondering if there was a way to do a SELECT statement inside the UPDATE statement, like this:
UPDATE `tbl_taxclasses` SET `regionscount` = [SELECT COUNT(regionsid) FROM `tbl_taxclasses_regions` WHERE classid = 1] WHERE classid = 1
我要到达这里,因为我不确定MySQL的坚固程度,但是到目前为止,我确实有最新版本. (5.5.15)
I'm reaching here, since I'm not sure how robust MySQL is, but I do have the latest version, as of today. (5.5.15)
推荐答案
结果证明我实际上是在猜对.
Turns out I was actually guessing right.
这有效:
UPDATE `tbl_taxclasses`
SET `regionscount` = (
SELECT COUNT(regionsid) AS `num`
FROM `tbl_taxclasses_regions`
WHERE classid = 1)
WHERE classid = 1 LIMIT 1
我只需要用括号()替换方括号[].
I just needed to replace my brackets [] with parenthesis ().
这篇关于更新查询中的MySQL子查询select语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!