Click here to Skip to main content
14,971,742 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
hello,

I have a database with two tables :

SQL
sarcmission :
id
name
car_id
.
.
.

sarcmissiontable :
id
mission_id
.


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.

SQL
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 


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)
Posted
Updated 29-Mar-15 13:31pm
v2
Comments
jgakenhe 29-Mar-15 22:35pm
   
UPDATE sarcmissiontable
SET car_id = (SELECT sarcmissiontable_1.car_id = sarcmission.car_id
FROM sarcmissiontable AS sarcmissiontable_1 INNER JOIN
sarcmission ON sarcmissiontable_1.mission_id = sarcmission.id)
ENG.Samy Sammour 30-Mar-15 0:26am
   
it gave me 'incorrect syntax near to " = " '
jgakenhe 30-Mar-15 16:35pm
   
Then fix it! It the same thing as the guy below me copied and pasted.

Try this:
SQL
UPDATE sarcmissiontable
SET sarcmissiontable.car_id =
(SELECT sarcmission.car_id FROM sarcmission
WHERE  sarcmissiontable.mission_id = sarcmission.id)
   
Comments
ENG.Samy Sammour 30-Mar-15 9:12am
   
thank you very much its works for me
Hi Can you please check the column which you are updating is primary key?
   
Comments
ENG.Samy Sammour 30-Mar-15 1:34am
   
no it is not ... and it has a default value = 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900