SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/22/2013 :  13:30:35  Show Profile  Reply with Quote
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

USA
283 Posts

Posted - 04/22/2013 :  13:44:47  Show Profile  Reply with Quote
Try using TOP 1 in your sub queries

The other way would be to use a join.

djj

Edited by - djj55 on 04/22/2013 13:45:45
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

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

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 04/23/2013 :  08:15:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/23/2013 :  08:36:38  Show Profile  Reply with Quote
"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 - 04/23/2013 :  10:12:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/23/2013 :  10:31:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/23/2013 :  10:36:47  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 04/23/2013 10:44:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000