UPDATE Statement - SQL Server Forums

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

Transact-SQL (2000)

UPDATE Statement

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 03:59:26

Firstly Thanks for looking at this thread.

I have a problem with a update statement:

update tblSource1 set Matched = (select a.Matched
from tblSource1 as a,
holdingtable as b
where a.rowid = b.rowid
and b.Matched = 100)

When i run it, it shows this error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

i need to update a column in tblsource and set all records in column matched to 100 from tblsource where the rows in holdingtable = 100

see the select statement above.

The select statement works but i cannot update the other table where the rows match.

Kind Regards

Pete.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-06 : 04:16:08

As error says that your query getting "a.Matched" is returning more than one value, you need to decide which value you want. You can use Top(1)or Max from result of your query to upadte "Matched" column.

petek
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 04:22:50

sunil,

thanks for your reply, but the problem i have is

i have two tables one (holdingtable) with 100 records and table 2 (tblsource) with 800 or so. the problem being i need to find the record in two where they match table one then update a column in table one with the value 100.

any ideas

Kind Regards

Pete.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-08-06 : 04:34:08

I am not able to get your problem.Can you provide some sample data?
From what i could understand, may be you can try
update holdingtable set Matched =
(select a.Matched
from tblSource1 as a Inner Join
holdingtable as b on a.rowid = b.rowid
)From C where holdingtable.Matched = 100

Not 100% sure this is what you want.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 04:36:13

like this...?

update a set Matched = 100
from tblSource1 as a join holdingtable as b on a.rowid = b.rowid

Em

petek
Posting Yak Master

192 Posts

Posted - 2008-08-06 : 04:56:56

Thanks all for your help.

elamcaster the code worked a treat.

You guys are the best

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources