CODEWITHSUNDEEP
phpmysqlwordpressselectwhere-clause
Jens Törnell
Jens Törnell

Reputation: 24778

MySQL - Select instead of update for testing

Background

I found some code on wordpress.org forum. I would like to modify it so the counter is set depending on if a custom field i set or not. In my case if a custom field has key "expired" and value = "1" it should not be counted.

MySQL

I my case the SQL will be rendered like this...

UPDATE wi1_term_taxonomy tt SET count = (SELECT count(p.ID) FROM wi1_term_relationships tr LEFT JOIN wi1_posts p ON (p.ID = tr.object_id AND p.post_type = 'product' AND p.post_status = 'publish') WHERE tr.term_taxonomy_id = tt.term_taxonomy_id) WHERE tt.taxonomy = 'color'

Custom field part missing

What is missing is the "where" part where the custom field is checked.

Question - Select the result?

To have the answer to the above might be to localized for the QA. My question is instead...

Can I change the query in a way I could test it fast with a select instead of update to see my result before I let it change anything? Else I need to find and reset the updated values every time.

What I've tried so far

Upvotes: 0

Views: 165

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would suggest that you test the query using:

SELECT tr.term_taxonomy_id, count(p.ID)
FROM wi1_term_relationships tr LEFT JOIN 
     wi1_posts p
     ON (p.ID = tr.object_id AND p.post_type = 'product' AND p.post_status = 'publish')

And that you do the update using a join:

UPDATE wi1_term_taxonomy tt join
       (SELECT tr.term_taxonomy_id, count(p.ID) as cnt
        FROM wi1_term_relationships tr LEFT JOIN 
             wi1_posts p
             ON (p.ID = tr.object_id AND p.post_type = 'product' AND p.post_status = 'publish')
        group by term_taxonomy_id
WHERE tt.taxonomy = 'color';

This allows you to use the exact same query, both for testing and updating.

Upvotes: 0

Peter Gluck
Peter Gluck

Reputation: 8236

Yes, you should be able to test your first inner SELECT query but you will need to substitute a test value for the tt.term_taxonomy_id match:

--UPDATE wi1_term_taxonomy tt SET count = ( /* This line is commented out by '--' */

  SELECT count(p.ID) FROM wi1_term_relationships tr 
  LEFT JOIN wi1_posts p ON 
    (p.ID = tr.object_id 
     AND p.post_type = 'product' 
     AND p.post_status = 'publish') 
  WHERE tr.term_taxonomy_id = <insert_test_value_here>

--) WHERE tt.taxonomy = 'color' /* This line is commented out by '--' */

Just insert a valid value for <insert_test_value_here> and it should work.

Upvotes: 1

Related Questions