Turning a successful MySQL query into an update

Question

I have a query which when run provides a satisfactory result, this query is as follows: -

SELECT DISTINCT(cp.payment_ref),COUNT(cp.status) as 'Amount Declined',cp.*
                FROM client_payments as cp
                WHERE DATE(cp.payment_date) = CURDATE()
                GROUP BY cp.payment_ref
                HAVING COUNT(cp.status='Declined')=3

What I want to do is update the results of the query with a tag, so what I need to do is turn the query into an update query which will update the result set. I cant get it to work.. here is my best attempt

UPDATE client_payments SET retry_status = 'retry' WHERE id=(SELECT cp.id ,COUNT(cp.status)
                FROM client_payments as cp
                WHERE DATE(cp.payment_date) = '2018-09-24'
                GROUP BY cp.payment_ref
                HAVING COUNT(cp.status='Declined')=3)

This throws an error of #1093 - You can't specify target table 'client_payments' for update in FROM clause

Any ideas?

Mohammad · Answer 1 · 2018-09-28T01:45:12.543

0

you need from Clause :

UPDATE client_payments SET client_payments.retry_status = 'retry' 
from(SELECT cp.id ,COUNT(cp.status)
            FROM client_payments as cp
            WHERE DATE(cp.payment_date) = '2018-09-24'
            GROUP BY cp.payment_ref
            HAVING COUNT(cp.status='Declined')=3) as myquery
WHERE client_payments.id=myquery.id

edited Sep 28 '18 at 01:45

answered Sep 28 '18 at 01:04

Mohammad

1,549
1
15
27

When run MySql throws an error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from(SELECT cp.id ,COUNT(cp.status) FROM client_payments as cp :( – Keith Ivison Sep 28 '18 at 01:35
let me check it – Mohammad Sep 28 '18 at 01:36
appreciated, thank you – Keith Ivison Sep 28 '18 at 01:42
how about now? i updated it. – Mohammad Sep 28 '18 at 01:45

score 0 · Answer 2 · answered Sep 28 '18 at 02:01

So the solution that works is as follows: -

UPDATE client_payments as cp1
INNER JOIN (SELECT cp.id ,COUNT(cp.status)
        FROM client_payments as cp
        WHERE DATE(cp.payment_date) = '2018-09-24'
        GROUP BY cp.payment_ref
        HAVING COUNT(cp.status='Declined')=3) as cp2 on cp1.id = cp2.id
SET cp1.retry_status = 'retry'

Thanks to Mohammad for the nudge and mellamokb in this post for the inspiration.

Turning a successful MySQL query into an update

2 Answers2