Updating the count each time a variable appears in another table

Question

I have the following table called staysafe with the following columns:

id | email | orders

The id and email columns have been populated.

I then want to go through the Wordpress postmeta table and count each time the email address appears in the row where "_billing_email" appears for the meta_key, then update the staysafe table with how many times each email address appeared.

The postmeta table is set out as:

meta_id | post_id | meta_key | meta_value

I'm using the following but keep getting a runtime error (the postmeta table is big!!), where am I going wrong?

update staysafe

set orders =
(select count(*) 
from wp_postmeta
where email = meta_value)

Please tag a DBMS (Oracle, MySQL, etc); that way, the question will be visible to people who are knowledgeable in your specific SQL dialect. Also, please include exactly what error you're getting. — Josh Eller, Jun 26 '20 at 12:59
@JoshEller Sorry updated now, I'm getting a runtime error as the table is so big. It hasn't factored in the "_billing_email" in my attempt so may be that would help??? — Rob, Jun 26 '20 at 13:21

score 0 · Answer 1 · answered Jun 26 '20 at 13:22

0

I believe that you want this:

update staysafe s
set s.orders = (
  select count(*) 
  from wp_postmeta
  where meta_key = '_billing_email' and meta_value = s.email
)

This will update all the rows of the table staysafe.
If you want to update a specific row then add a WHERE clause:

where s.email = 'someemail@aaa.com'

answered Jun 26 '20 at 13:22

forpas

160,666
10
38
76

Thanks, that looks like it'd work but I keep hitting a timeout, is there a way around it? 'Lock wait timeout exceeded; try restarting transaction Error code 1205.' – Rob Jun 26 '20 at 13:27
A composite index on (meta_key, meta_value) in the table wp_postmeta would help to speed up the process. For the timeout error that you get there are several questions/answers here in SO: https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im, https://stackoverflow.com/questions/36547173/error-code-1205-lock-wait-timeout-exceeded-try-restarting-transaction-on-de and others. – forpas Jun 26 '20 at 13:32

score 0 · Answer 2 · answered Jun 26 '20 at 13:38

You can aggregate and join:

update staysafe s left join
       (select meta_value, count(*) as cnt
        from wp_postmeta
        where meta_key = '_billing_email'
        group by metavalue
       ) pm
       on s.email = pm.email
    set orders = coalesce(cnt, 0);

If you don't care about 0 values, then change the left join to an inner join.

Updating the count each time a variable appears in another table

2 Answers2