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.
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 errorSubquery 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 queriesThe other way would be to use a join.djj |
|
|
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? |
|
|
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 |
|
|
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 recordCheersMIK |
|
|
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_addressThe problem is the query returns multiple rows. Is there another way to get around this? |
|
|
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 thisselect TOP 1 c.product_id from thirdparty_table a inner join Email c on a.Email = c.email_addressIs there a relationship between products and either thirdparty_table or Email?JimEveryday I learn something that somebody else already knew |
|
|
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_addressGroup by a.email having count(distinct c.product_id)>1Returns 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<>0CheersMIK |
|
|
|
|
|
|
|