updating a table in sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

updating a table in sql server

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-27 : 12:21:52

Scenario
Added 2 new columns to an existing table where Id is the unique key
Want to update the new columns based on the unique key.There are almost 3 rows in the table that needs to get updated.

Can someone help me with the sql to update the table

Thanks
Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 12:30:14

What values should be updated?

Madhivanan

Failing to plan is Planning to fail

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-05-27 : 13:16:36

can you show the your table structure with some data?

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-27 : 16:37:56

we have tabla A with columns 1,2,3
we want to add extra columns say 4,5 to Table A and update columns 4,5 from Table B
the Join between Table A and table B is column 1

The Above is exactly the scenario.I can give the table structure

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 02:56:04

update t1
set col4=t2.col4,col5=t2.col5
from table1 as t1 inner join table2 as t2
on t1.col1=t2.col1

Madhivanan

Failing to plan is Planning to fail

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-28 : 09:39:12

Iam getting the below error message when trying to use the above query

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "contact1.windowsID" could not be bound.

Note:contact1.windowsID is the name of the column from the temp table that we want to copy to our existing contact table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 10:38:08

quote:
Originally posted by jim_jim

Iam getting the below error message when trying to use the above query

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "contact1.windowsID" could not be bound.

Note:contact1.windowsID is the name of the column from the temp table that we want to copy to our existing contact table

Post the full code you used

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-28 : 10:39:17

Please show us the exact query that you tried.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-05-28 : 12:15:55

Finally I tried the below code and it worked

UPDATE j1

SET j1.windowsID = j2.windowsID
FROM contact j1

INNER JOIN contact1 j2

ON j1.contact_id = j2.contact_id

Thanks Vijay and madhivanam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources