54

I am trying to create an update query and making little progress in getting the right syntax. The following query is working:

SELECT t.Index1, t.Index2, COUNT( m.EventType ) FROM Table t LEFT JOIN MEvents m ON (m.Index1 = t.Index1 AND m.Index2 = t.Index2 AND (m.EventType = 'A' OR m.EventType = 'B') ) WHERE (t.SpecialEventCount IS NULL) GROUP BY t.Index1, t.Index2

It creates a list of triplets Index1,Index2,EventCounts. It only does this for case where t.SpecialEventCount is NULL. The update query I am trying to write should set this SpecialEventCount to that count, i.e. COUNT(m.EventType) in the query above. This number could be 0 or any positive number (hence the left join). Index1 and Index2 together are unique in Table t and they are used to identify events in MEvent.

How do I have to modify the select query to become an update query? I.e. something like

UPDATE Table SET SpecialEventCount=COUNT(m.EventType).....

but I am confused what to put where and have failed with numerous different guesses.

Improve this question

3 Answers 3

Reset to default
95

I take it that (Index1, Index2) is a unique key on Table, otherwise I would expect the reference to t.SpecialEventCount to result in an error.

Edited query to use subquery as it didn't work using GROUP BY

UPDATE Table AS t LEFT JOIN ( SELECT Index1, Index2, COUNT(EventType) AS NumEvents FROM MEvents WHERE EventType = 'A' OR EventType = 'B' GROUP BY Index1, Index2 ) AS m ON m.Index1 = t.Index1 AND m.Index2 = t.Index2 SET t.SpecialEventCount = m.NumEvents WHERE t.SpecialEventCount IS NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks, looks similar to many things I have tried. Unfortunately comes back with the following error: #1064 - 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 'GROUP BY t.Index1, t.Index2' at line 9
using version 5.0.90-log if this helps
Problem with above is differentiating between 0s and NULLs. If there are no Events matching, then t.SpecialEventCount is set to NULL and not 0, thus you always end up with lots of NULLs in your table where you dont know whether these are zeros or have not been calculated yet. Tried Set ... = IF(m.NumEvents IS NULL,0,m.NumEvents) without success
Change SET t.SpecialEventCount = m.NumEvents to SET t.SpecialEventCount = IFNULL(m.NumEvents, 0)
nice, it works fine the UPDATE using LEFT JOIN statement, otherwise not
|
6

Doing a left join with a subquery will generate a giant temporary table in-memory that will have no indexes.

For updates, try avoiding joins and using correlated subqueries instead:

UPDATE Table AS t SET t.SpecialEventCount = ( SELECT COUNT(m.EventType) FROM MEvents m WHERE m.EventType in ('A','B') AND m.Index1 = t.Index1 AND m.Index2 = t.Index2 ) WHERE t.SpecialEventCount IS NULL

Do some profiling, but this can be significantly faster in some cases.

Improve this answer

1 Comment

Profiling in advance on update statements is not widely supported, so often it has to be actually executed to test it. An update of all 119465 rows in a table joined in this way to a table of 1.8 milion rows took 0.7680 seconds using your method, and 0.6227 seconds using Hammerite's method. No drama there, i would say. Please do note that this could be situation specific. I gave it another try linked to a 18 mil table, there the numbers where 23.9238 seconds and 9.1261 seconds respectively. No improvements.
1

my example

update card_crowd as cardCrowd LEFT JOIN ( select cc.id , count(1) as num from card_crowd cc LEFT JOIN card_crowd_r ccr on cc.id = ccr.crowd_id group by cc.id ) as tt on cardCrowd.id = tt.id set cardCrowd.join_num = tt.num;
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.