CODEWITHSUNDEEP
phpmysqldatabasecsv
Geoff_S
Geoff_S

Reputation: 5105

SQL filling one table's fields with values from another table

I'm fairly new to SQL (Mostly building tables and modifying, with some select/insert statements along the way) but I have a task at hand that I'm stuck on.

I've been filling html tables on one of my pages with database info through PHP. Everything fills up correctly, but I need to populate some values in my database table so that I can show them in the HTML table.

I have a large staging table and a smaller pricing table. When a CSV file is uploaded on the site I have it inserted into the staging table. I want to find a way to fill up the pricing fields from info in the pricing table.

The prices are built from three things (all of which are in the staging table): meterType, meterSize and workOrderType2. There is another field in my staging table called onsiteSurveyTestCost.

My pricing table contains the priceID, meterSize, meterType, workOrderType, and price.

So, if a CSV is inserted and it has '3"' for meterSize, 'Compound' for meterType and 'Survey and Test' for workOrderType2 then I want it to use priceID '1' which has 3" Compound Survey and Test and I want to save the price into the original staging table as onsiteSurveyTestCost.

I hope that makes sense. Even if it can't be done upon INSERT/CSV upload, I still need to find a way to do this on my form page so that I can display the proper price for the form.

Pricing Table:

enter image description here

Staging Table Fields:

enter image description here enter image description here

UPDATE:

Incomplete SQL statement:

UPDATE staging 
SET onsiteTestSurveyPrice = (
SELECT price from pricing WHERE=
/* meterType of staging matches meterType of pricing*/
/* meterSize of staging matches meterSize of pricing*/
/* workOrderType2 of staging matches workOrderType of pricing*/

)

Upvotes: 0

Views: 932

Answers (1)

Don't Panic
Don't Panic

Reputation: 41820

You should be able to do the update by joining your staging table to the pricing table on those columns.

UPDATE staging
INNER JOIN pricing ON
    staging.meterType = pricing.meterType
    AND staging.meterSize = pricing.meterSize
    AND staging.workOrderType2 = pricing.workOrderType
SET staging.onsiteTestSurveyPrice = pricing.price

There may be a better way to do this, but it's hard to tell without knowing more about how your application works.

Upvotes: 1

Related Questions