将表中的值插入另一个表中

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_price和ms_name的值插入到另一个表中，该表是service_type和service_amount列的记录。

ms_pricelist

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).

我只想更新一个现有的记录，它将ms_name和ms_price的值（来自ms_pricelist表）插入到service_type和service_amount（记录表）中。

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

1 个解决方案

#1

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

你想要一个MERGE语句（如果你支持它的数据库）：

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:

如果你使用的数据库引擎没有MERGE，那么你必须采用老式的方式;使用单独的INSERT和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

#1