Subquery returned more than 1 value ?

[delaap]

Hi,

I'm trying to update a table with the following command:

UPDATE tt_Contact
SET tt_Contact.OrganisationCode = Organisation_retained_replaced.retain
FROM Organisation_retained_replaced
WHERE Organisation_Retained_replaced.Uniq = tt_Contact.OrganisationCode

The error I get is this:
"Server: Msg 512, Level 16, State 1, Procedure UpdateDate_tt_Contact, 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.&quot;

I have unique values in the Organisation_retained_replaced table, which link to non uniq values in tt_Contact.

I'm sure it is a simple query to write but I've been looking at it for ages now and its time to ask for some help.
Many Thanks
Matt

 

[tlbroadbent]

You need to create a JOIN to identify the relationship between tt_Contact and Organisation_retained_replaced.

For example:

UPDATE tt_Contact
SET tt_Contact.OrganisationCode = Organisation_retained_replaced.retain

FROM tt_contact Inner Join Organisation_retained_replaced
ON tt_contact.<somecol> = Organisation_retained_replaced.<relatedcol>

WHERE Organisation_Retained_replaced.Uniq Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[delaap]

Hi Terry,

When I run the following query:
select * from tt_Contact, Organisation_Retained_replaced
where Organisation_Retained_replaced.Uniq = tt_contact.organisationcode

I get:

tt_Contact.OrganisationCode Organ ~~~~~ Retain
82161444 82161444 91182800
82161444 82161444 91182800
82161444 82161444 91182800

But all these records are unique on the Primary Key.

I then run your Join incoporated query and I recieve the same result as before:
&quot;Server: Msg 512, Level 16, State 1, Procedure UpdateDate_tt_Contact, 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.&quot;


Any other ideas ?
Thanks for you help

 

[tlbroadbent]

Which version of SQL Server are you running? Your first query works in SQL 2000 as does my recommendation. You might try the following.

Update tt_Contact Set organisationcode=a.Retain
From (Select Uniq, Retain=Max(Retain) From Organisation_Retained_replaced Group By Uniq) a
Where a.Uniq = tt_contact.organisationcode Terry Broadbent
Please review faq183-874.

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[delaap]

Hi Terry,

The query didn't work until I removed a trigger, I was looking at the error and the top looks like this:
&quot;Server: Msg 512, Level 16, State 1, Procedure UpdateDate_tt_Contact, Line 10&quot;

My procedure UpdateDate_tt_Contact somehow causes conflict with an update statement, I removed the procedure and the script ran succesfully. I now will look into why it causes conflict.
Many thanks for input, greatly appreciated.
Matt