Single Column Update by result set

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

SQL Server 2000 Forums

SQL Server Development (2000)

Single Column Update by result set

Author

Topic

senthil_red
Starting Member

1 Post

Posted - 2007-09-18 : 04:38:51

Hi,
i have one problem in query. i wnt to update one column in table by inserting another column of other table.

1) create table table1 (col1 integer)
2) insert into table1(col1) select col1 from table2
3) alter table table1 add col2 integer
4) update table1 set col2 in (select col2 from table2 where
table2.id=7) where table1.col2 is NULL

In this subquery returns more than one value. i tried out this query but it shows error as incorrect syntax near keyword 'in' and incorrect syntax near keyword 'where'.

My aim is to update table1.col2 in single update by inserting col2 from table2. How to do this? please help me.

-senthil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 04:55:04

This is your query as of now. It updates ALL records in table1 with the value from table2

update		t1
set		t1.col2 = t2.col2
from		table1 as t1
inner join	table2 as t2 on t2.id = 7
where		t1.col2 is null

I think you need

update		t1
set		t1.col2 = t2.col2
from		table1 as t1
inner join	table2 as t2 on t2.id = t1.id
where		t1.col2 is null
and		t2.col2 = 7

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources