Updating a lookup table without losing old data

[steveinid1]

I have two tables. one for gas stations and another for gas type. to keep it simple the first table has 3 stations: Chevron, Texaco, and Citgo. The second table has 2 gas types: Regular and unleaded. I ran this sql query:

CREATE TABLE prices as
SELECT station.sid, gas.gid
FROM station
JOIN gas

I got the expected output so I then altered the table and added a new column called gas_price. I now have the following:

SID GID GAS_PRICE
1 … 1
1 … 2
2 … 1
2 … 2
3 … 1
etc…

This is exactly what I want. I can then run the following:

UPDATE prices
SET current_price= 2.99
WHERE SID = 2 AND GID = 1;

So now the price for regular gas at texaco is $2.99 and this is reflected in the prices table like so:

SID GID GAS_PRICE
1…1
1…2
2 …1… 2.99
2 …2
3 …1

Now this is where it is getting complicated for me. The gas stations will have addresses associated with them so there could be thousands of gas stations. So every time I add a gas station to the “STATION” table I do not want to DROP the PRICES table and then re-run the “CREATE TABLE PRICES AS” command because I will then lose all of the price info which will be entered via a form by users. (I know there is a site called gas buddy that does this already… I’m trying to build a similar site) I want to be able to run a query that will add the gas stations to the “PRICES” table without losing that data. How would I do that?

Thanks for the help.

[DarthGuido]

https://dev.mysql.com/doc/refman/5.7/en/insert.html