CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Jamie Hall
Jamie Hall

Reputation: 102

SQL UPDATE Automation

I have a query in the form of an UPDATE statement. Lets say it's open rate for an email campaign, in this example the city is London.

I want to run this query for 80 cities every month and have the results represented in 2018_Cities table. Is there a way I can automate this so I don't have to type all the cities every month?

Don't feel obliged to supply code you can just point me in the direction of the process if you want and I can investigate but I'm struggling to find the words to ask Google!

UPDATE 2018_Cities SET openRate = (
SELECT ROUND((
SELECT (
SELECT COALESCE( SUM(opens),0) 
FROM 0118
WHERE siteshort 
IN('London')) + (
SELECT COALESCE( SUM(opens),0) 
FROM 0218
WHERE siteshort 
IN('London')) 
as result) / 
(SELECT (
SELECT COALESCE( SUM(recipients),0) 
FROM 0118
WHERE siteshort 
IN('London')) + (
SELECT COALESCE( SUM(recipients),0) 
FROM 0218
WHERE siteshort 
IN('London')) 
as result) * 100, 1)) WHERE siteshort = ('London');

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Presumably, you want something like this:

UPDATE 2018_Cities c JOIN
       (SELECT siteshort, SUM(opens) / NULLIF(SUM(RECIPIENTS), 0) as new_openrate
        FROM `0118` t
        GROUP BY siteshort
       ) t
       ON c.siteshort = t.siteshort
    SET c.openRate = new_openrate;

Your logic is rather hard to follow, but the idea is the same. Write a subquery to produce the calculations. Then use JOIN to bring in the new values.

Note that 0118 is a ridiculous table name. You should fix the names in your data model.

Upvotes: 1

Related Questions