CODEWITHSUNDEEP
mysqlsqlgroup-bysql-update
Jimmery
Jimmery

Reputation: 10139

MYSQL - Using Update and Group By together

This SELECT statement gives me the results I want:

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

For these results I want to set submission_archive.status_id=9 but when I try:

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

I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'GROUP BY submission_archive.submission_id'

After some looking about, I suspected I might need to use a subquery to achieve the results I want. I have never done subquerys before, but I had a go with this:

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  
       )

And that got me this error:

Table 'submission_archive' is specified twice, both as a target for 'UPDATE' and as a separate source for data

I can perform the UPDATE statement without the GROUP BY statement, but I need to update only 1 of the records that have matching submission_ids - it doesn't matter which one, but it has to be just one of them.

Can anyone help me achieve what I want? Many thanks.

Upvotes: 1

Views: 45

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

Correct syntax should like this :

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

containing [INNER] JOIN before the subquery instead of WHERE clause.

Upvotes: 1

Related Questions