CODEWITHSUNDEEP
mysqlwordpressjoinsql-updatewoocommerce
NotAnExpert
NotAnExpert

Reputation: 35

Update column value by adding 2 column values together using a select an join query

I am looking for a way to update a 3rd column, by adding the values of 2 other columns together. The problem I am running into is that the UPDATE statement seems to need a table specified, but I am using a "virtual" table by doing SELECT and JOIN statements. Here is the code I currently have:

   SELECT *
     FROM wp_posts      AS p
LEFT JOIN (
            SELECT tr.object_id AS id, 
                   t.name       AS physical
              FROM wp_term_relationships AS tr  
        INNER JOIN wp_term_taxonomy AS x 
                     ON (x.taxonomy='pa_physical-inventory' 
                    AND x.term_taxonomy_id=tr.term_taxonomy_id)
        INNER JOIN wp_terms AS t 
                     ON t.term_id=x.term_id    
          ) AS mo ON p.id = mo.id
LEFT JOIN (
            SELECT tr.object_id AS id, 
                   t.name       AS murphy
              FROM wp_term_relationships AS tr  
        INNER JOIN wp_term_taxonomy AS x 
                     ON (x.taxonomy='pa_murphy-inventory' 
                    AND x.term_taxonomy_id=tr.term_taxonomy_id)
        INNER JOIN wp_terms AS t 
                     ON t.term_id=x.term_id 
          ) AS pa ON p.id = pa.id
LEFT JOIN (
           SELECT post_id AS id, meta_value AS totalinventory
             FROM wp_postmeta
            WHERE meta_key = '_stock'
          ) AS totalinventory ON p.id = totalinventory.id
    WHERE p.post_status = 'publish' 
      AND p.post_type = 'product'

I am looking to add "murphy" and "physical" together and insert into "totalinventory" for each row that is returned. Some of these rows return "null" (like if "murphy" has no stock set it shows null) so I am looking to also take that as "0" when adding the values together.

Any help or guidance would be greatly appreciated as I have been scratching my head over this for far too long.

EDIT: I am open to PHP in the solution because eventually this will be run using a cron job.

Upvotes: 0

Views: 1544

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

In simpler cases, SELECTs can usually be converted to UPDATEs like so...

The SELECT:

SELECT * 
FROM [tables and joins]
WHERE [conditions]
;

The UPDATE:

UPDATE [tables and joins (as above)]
SET tableA.fieldB = tableC.fieldD + tableE.fieldF
WHERE [conditions (same as above)]
;

Of course, if you have LEFT JOINs you'll need to compensate if necessary; I find it best to SELECT all the pieces you'll need for the SET first, before making the conversion to an UPDATE query.

Disclaimer: As I prefaced, depending on circumstances and the particulars of the data, this doesn't always work.

Upvotes: 0

Related Questions