时间戳

首页 > 解决方案 > MYSQL - 一起使用更新和分组

mysql - MYSQL - 一起使用更新和分组

问题描述

这个SELECT语句给了我想要的结果：

SELECT submission_archive.id,
       submission_archive.status_id,
       lead.status_id AS job_status_id,
       submission_archive.submission_id,
       lead.id AS lead_id
  FROM submission_archive
  JOIN record
    ON submission_archive.record_id = record.id
  JOIN lead
    ON record.lead_id = lead.id
 WHERE submission_archive.status_id = 1
   AND lead.status_id = 9
 GROUP BY submission_archive.submission_id

对于这些结果，我想设置submission_archive.status_id=9但是当我尝试时：

UPDATE submission_archive 
  JOIN record 
    ON submission_archive.record_id = record.id 
  JOIN lead 
    ON record.lead_id = lead.id
   SET submission_archive.status_id = 9
 WHERE submission_archive.status_id = 1
   AND submission.status_id = 9 
 GROUP BY submission_archive.submission_id

我收到以下错误：

您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以获取在“GROUP BY submit_archive.submission_id”附近使用的正确语法

经过一番查看，我怀疑我可能需要使用子查询来实现我想要的结果。我以前从未做过子查询，但我尝试过：

UPDATE submission_archive
   SET status_id=9
 WHERE (
        SELECT submission_archive.id
          FROM submission_archive
          JOIN record 
            ON submission_archive.record_id=record.id
          JOIN lead 
            ON record.lead_id=lead.id
         WHERE submission_archive.status_id=1 AND lead.status_id=9
         GROUP BY submission_archive.submission_id  
       )

这给了我这个错误：

表“submission_archive”被指定了两次，既作为“UPDATE”的目标，又作为数据的单独源

我可以在UPDATE没有该语句的情况下执行该GROUP BY语句，但我只需要更新 1 个匹配submission_ids 的记录 - 哪一个并不重要，但它必须只是其中之一。

谁能帮我实现我想要的？非常感谢。

标签： mysqlsqlgroup-bysql-update

解决方案

正确的语法应该是这样的：

UPDATE submission_archive sa1
  JOIN
     (
        SELECT sa.id
          FROM submission_archive sa
          JOIN record r
            ON sa.record_id = r.id
          JOIN lead l
            ON r.lead_id = l.id
         WHERE sa.status_id = 1
           AND l.status_id = 9
         GROUP BY sa.submission_id
       ) sa2   
    ON sa1.id = sa2.id
   SET sa1.status_id = 9

包含[INNER] JOIN在子查询而不是WHERE子句之前。

推荐阅读