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 2008 Forums
 Transact-SQL (2008)
 Update query help

Author  Topic 

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-22 : 13:30:35
update products set
product_id = (select c.product_id from thirdparty_table a inner join Email c on a.Email = c.email_address)
where exists (select product_id from productswhere id != '0')

when i run the above query, I get the following error
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.


What needs to be done?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-22 : 13:44:47
Try using TOP 1 in your sub queries

The other way would be to use a join.

djj
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-22 : 14:28:24
How to use top 1 and how to use an other join?
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-23 : 08:15:59
To use the TOP 1:
update products set 
product_id = (select TOP 1 c.product_id from thirdparty_table a inner join Email c on a.Email = c.email_address)
where exists (select product_id from products where id != '0')


As to the join, I did not realize that the sub query was not directly related to the products table. This may be the cause of your problems.

djj
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 08:36:38
"The need" that should be done is to define the criteria for updating product_id field. What you're currently doing is just updating it with Email.Product_ID based on the condition of Email. So, if Email is unique in both tables then the inner select statment would return a single Product_ID and would update the field, else there would be multiple product_id returned as a result of the inner query and how come you set multiple product_IDs a field of one record

Cheers
MIK
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 2013-04-23 : 10:12:43
So how do I set the product_id to the the product_id that I will get from
select TOP 1 c.product_id from thirdparty_table a inner join Email c on a.Email = c.email_address

The problem is the query returns multiple rows. Is there another way to get around this?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-04-23 : 10:31:34
There is no relationship between the table you are updating and the criteria you updating it with. Your current query will update everything in the products table that doesn't have an id of '0' (is id really a string?!) with the first thing it finds in this
select TOP 1 c.product_id from thirdparty_table a inner join Email c on a.Email = c.email_address

Is there a relationship between products and either thirdparty_table or Email?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-23 : 10:36:47
If this query

select a.email,count(distinct c.product_id) from thirdparty_table a inner join Email c on a.Email = c.email_address
Group by a.email having count(distinct c.product_id)>1

Returns 0 rows, then its fine to update the field with "top 1".

Still fine to use "top 1", if the above query returns records and you're requirements are just to update the products.product_ID with any one (randomly selected) Email.product_Id.

However, if the above query returns records and you want a specific email.product_ID to be updated then you need to adjust your query accordingly. that is why I said "to define the criteria for updating product_ID field".

additionally, by where condition in your original post, you might actually meant to be ID <>0, and would like to use it as

where exists (select product_id from productswhere id != '0')
WHERE ID<>0

Cheers
MIK
Go to Top of Page
   

- Advertisement -