Mysql question about UPDATE

Question

UPDATE counter_reports 
SET `counter`=`counter`+1,`date`=?
WHERE report_id IN(
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `year`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `month`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `week`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `day`=1 
                    ORDER BY report_id DESC LIMIT 1)
                  )

Is there any alternative for such sql? I need to update(increment by 1) last counter reports for day,week,month and year.

If I'm adding manually, sql works fine, but with subqueries it fails to launch.

Thanks. :)

Answer 1

What is not working and what is the error?

The above sub-queries could be a bit improved

SELECT report_id 
FROM counter_reports 
WHERE report_name="emails_sent" AND `year`=1 
ORDER BY report_id DESC LIMIT 1

is equivalent to

SELECT max(report_id)
FROM counter_reports
WHERE report_name="emails_sent" AND `year`=1

and in case there is an index over report_name, year and report_id it could be fast.

EDIT: In case you are running into ERROR 1093 (HY000): You can't specify target table 'table_name' for update in FROM clause there is a workaround for that.

Generally speaking the above is a bit ugly and I guess it will not show signs of becoming prettier. One of the ways to deal with the above, and especially since this is (obviously?) a part of a multi-step procedure, is to store these four id's in certain transition table where they could be reused by different parts of the process to produce the reports.

Alternatively keeping track of the ids on the application side would also be efficient (passing them as parameters to function that would update them, etc..).

Answer 2

MySQL is kinda lame, do this, that will work:

UPDATE counter_reports 
SET `counter`=`counter`+1,`date`=?
WHERE report_id IN(
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `year`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `month`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `week`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `day`=1 
                    ORDER BY report_id DESC LIMIT 1) as x)
                  )

Also take a look at last example here (Mysql code, related to your problem): http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html