CODEWITHSUNDEEP
mysqlsqlselectjoininsert-into
Joshua Bautista
Joshua Bautista

Reputation: 43

Insert value from table into another table

I'm trying to insert the value of ms_price and ms_name to another table which is records for the service_type and service_amount column.

ms_pricelist

| ms_priceID | ms_Name | ms_price |
|------------|---------|----------|
| 7894       |Casing   | 17.0897  |
| 7895       |Fan      | 69.9902  |

records

| customerID | service_type | service_amount |
|------------|--------------|----------------|
| 1000712    | Casing       |  17.2311       |
| 1000712    | Trunion      | 189.9900       |

Here's my code.

INSERT INTO records (service_type, service_amount)
    SELECT ms_pricelist.ms_name, ms_pricelist.ms_price
    FROM records JOIN
         ms_pricelist
    WHERE records.CustomerNumber='1' AND ms_pricelist.ms_priceID='1'

It does what I want but it adds a new record(edit history: column). I want to UPDATE or INSERT it into an existing record in my records table.

EDIT

I just want to UPDATE an existing record that will insert the value of ms_name and ms_price (from ms_pricelist table) TO service_type and service_amount (records table).

So that in the end the records table would contain:

| customerID | service_type | service_amount |
|------------|--------------|----------------|
| 1000712    | Casing       |  17.0897       | <--Note: updated price
| 1000712    | Trunion      | 189.9900       |
| 1000712    | Fan          | 69.9902        | <--Note: new row

Upvotes: 0

Views: 203

Answers (1)

Ian Boyd
Ian Boyd

Reputation: 256641

You want a MERGE statement (if the database you have supports it):

CREATE TABLE ms_pricelist (
  ms_priceID int,
  ms_Name nvarchar(50),
  ms_price money
)

CREATE TABLE  records ( 
    customerID int,
    service_type nvarchar(50),
    service_amount money
  )

 MERGE records
 USING (
     SELECT 1000712 AS customerID, ms_name, ms_price
     FROM ms_pricelist
     wHERE ms_priceID = 7894
     ) AS rows(CustomerID, Name, Price)
 ON records.service_type = rows.Name
 AND records.customerID = 1000712
 WHEN MATCHED THEN UPDATE 
     SET service_amount = rows.Price
 WHEN NOT MATCHED BY TARGET THEN INSERT(customerID, service_type, service_amount)
     VALUES (rows.CustomerID, rows.Name, rows.Price);

If you the database engine you're using doesn't have MERGE then you have to do it the old fashioned way; with separate INSERT and UPDATE:

--Add any missing rows
INSERT INTO records (customerID, service_type, service_amount)
SELECT 1000712 AS CustomerID, ms_name, ms_price
FROM ms_pricelist
WHERE ms_priceID = 7894
AND NOT EXISTS (
    SELECT * FROM records 
    WHERE records.service_type = ms_pricelist.ms_name
    AND records.customerID = 1000712)

--Update any existing rows
UPDATE records
SET service_amount = ms_pricelist.ms_price
FROM records
    INNER JOIN ms_pricelist
    ON records.service_type = ms_pricelist.ms_name
WHERE ms_pricelist.ms_priceID = 7894
AND records.customerID = 1000712

http://rextester.com/FEGQS79621

Upvotes: 1

Related Questions