Update statement not valid - 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

General SQL Server Forums

New to SQL Server Programming

Update statement not valid

Author

Topic

ktavolacci
Starting Member

3 Posts

Posted - 2009-02-27 : 01:42:56

Hi all, forgive my ignorance...I'm trying to update all the values in column 1 from table A with values
in column 2 from table B, but no matter the syntax I try I get this error messge:

Server: Msg 512, Level 16, State 1, Procedure Found_Insert_Update, Line 10
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.

Obviously, I'm just not getting it. I can do it in a Query in Access but not in SQL Query Analyzer. This is my statement:

UPDATE tbl_Found
set Location_Found = t.trnslate
FROM tbl_Found f INNER JOIN tbl_Found_Location2 t
On f.Location_Found = t.Found_Location

Table tbl_Found has column Location_Found which matches values in
Table tbl_Found_Location2 with column Found_Location

I want to update tbl_Found.Location_Found to the values of column tbl_Found_Location2.trnslate

Why does this below statement work in Access, but not SQL Server?

UPDATE tbl_Found INNER JOIN tbl_Found_Location2
ON tbl_Found.Location_Found = tbl_Found_Location2.Found_Location
SET tbl_Found.Location_Found = tbl_Found_Location2.trnslate

thanks, Kathleen

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-27 : 01:55:13

try this

UPDATE f
set f.Location_Found = t.trnslate
FROM tbl_Found f INNER JOIN tbl_Found_Location2 t
On f.Location_Found = t.Found_Location

shaihan
Starting Member

20 Posts

Posted - 2009-02-27 : 02:01:23

the problem is your tbl_Found_Location2 has multiple rows containing same value in the Found_Location field. as a result you get multiple values returned for the trnslate field. try to use combination of unique/primary key to make the statement return a single value to assign.

S ]-[ /-\ | ]-[ /-\ N

ktavolacci
Starting Member

3 Posts

Posted - 2009-02-27 : 13:02:59

Raky, I still get the same error message with that update statement.

Shaihan, tbl_Found_Location2 is a lookup table and every row is unique and every value in the column Found_Location is unique as well.

ktavolacci
Starting Member

3 Posts

Posted - 2009-02-27 : 16:32:58

Found the problem--although it doesn't make sense to me.

There is a trigger on tbl_Found to create a tracking record into tbl_tracking anytime there is an insert or update on tbl_found.

When I deleted the trigger, the update statement worked.

So, now I'll just put the trigger back.

It doesn't make sense to me because I don't see what is being violated by inserting a record for each update on tbl_found into tbl_tracking.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources