update table from another table according to join relation sql server

Question

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 29-Mar-15 12:33pm

ENG.Samy Sammour

Updated 29-Mar-15 13:31pm

v2

Add a Solution

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.

2 solutions

Solution 1

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

Posted 29-Mar-15 19:25pm

Zubairk1990

Comments

ENG.Samy Sammour 30-Mar-15 1:34am

no it is not ... and it has a default value = 1

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Peter Leow · Accepted Answer · 2015-03-30T00:44:00

Solution 2

Try this:

SQL

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

Posted 30-Mar-15 0:44am

Peter Leow

Comments

ENG.Samy Sammour 30-Mar-15 9:12am

thank you very much its works for me