This blog post has moved. Please find it at:
http://www.mpopp.net/2005/09/insert-on-duplicate-key-update/.
Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
2 comments:
Nice Post! Could you kindly help me with a problem. I have this query: "SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC LIMIT 10"
It works great -- It displays the count for each term in the table. However, I added a field called "count" and I would also like to update all of the rows currently in the table with their count. Any help would be Greatly appreciated.
Best,
Ryan
Hi,
If it's a one-time task, I think the easiest way is to materialize the query and temporarily create a table which you use to join to the logs table and update it with the count, like this:
CREATE TABLE logs_cnt
SELECT *, COUNT(term) AS count_term FROM logs GROUP BY term HAVING count_term>1 AND term NOT LIKE 'Enter%' AND term NOT LIKE '' ORDER BY count_term DESC;
ALTER TABLE logs_cnt ADD PRIMARY KEY(term);
UPDATE logs a INNER JOIN logs_cnt b USING (term)
SET a.count = b.count_term;
DROP TABLE logs_cnt;
I hope that helps ;).
Markus
Post a Comment