更新查询中的MySQL子查询select语句 [英] MySQL sub query select statement inside Update query

问题描述

我有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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！