根据连接关系sql server从另一个表更新表 [英] update table from another table according to join relation sql server

问题描述

你好，



我有一个包含两张桌子的数据库：

hello,

I have a database with two tables :

sarcmission :
id
name
car_id
.
.
.

sarcmissiontable :
id
mission_id
.

我想要将car_id添加到第二个sarcmissiontable但这个car_id在sarcmission应该是相同的



我有3500条记录所以我想做一个带有car_id的sql更新从第一个表（sarcmission）并根据连接关系（sarcmission.id = sarcmissiontable.mission_id）更新第二个表（sarcmissiontable）中的car_id。< / pre>



如何在不丢失数据的情况下这样做。

I want to add car_id to the second one sarcmissiontable but this car_id should be the same at sarcmission

and I have 3500 record so I want to make an sql update that take the car_id from the first table (sarcmission) and update the car_id from the second table (sarcmissiontable) according to the join relation (sarcmission.id = sarcmissiontable.mission_id).</pre>

how can I do it with out losing the data.

I've tried this :
UPDATE sarcmissiontable
SET          sarcmissiontable_1.car_id = sarcmission.car_id
FROM     sarcmissiontable AS sarcmissiontable_1 INNER JOIN
                  sarcmission ON sarcmissiontable_1.mission_id = sarcmission.id 

但它给了我那个错误：列或表达式 car_id'无法更新。



但该列已存在且表格的名称也是如此。



PS我正在使用sql server 2008和visual studio 2010（asp.net - c＃code behind）

but it gave me that error : "Column or expression 'car_id' cannot be updated."

but the column is exist and the name of the table also true.

P.S. I am using sql server 2008 with visual studio 2010 (asp.net - c# code behind)

推荐答案

试试这个：

Try this:

UPDATE sarcmissiontable
SET sarcmissiontable.car_id =
(SELECT sarcmission.car_id FROM sarcmission
WHERE  sarcmissiontable.mission_id = sarcmission.id)

您好，请您检查您要更新的列是主键吗？

Hi Can you please check the column which you are updating is primary key?

这篇关于根据连接关系sql server从另一个表更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！