Select within update

When am I missing in order to get the below working?

Update dbo.Name
Set CombineID = NULL
where
SELECT CombineID
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1
PeterErhardAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did you try the Queries i have given
For testing purpose run this

BEGIN TRAN

SELECT CombineID, Count(*) as [Count]
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1

Update dbo.Name
Set CombineID = NULL
where CombineID In (
SELECT CombineID
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1 )

SELECT CombineID, Count(*) as [Count]
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1

ROLLBACK TRAN
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi PeterErhard,


Update dbo.Name
Set CombineID = NULL
where CombineID In (
SELECT CombineID
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1 )





Cheers!
0
 
PeterErhardAuthor Commented:
Thanks, but get this now:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Aneesh RetnakaranDatabase AdministratorCommented:
can i know the exact query you tried ?
0
 
PeterErhardAuthor Commented:
As above and below:

Update dbo.Name
Set CombineID = NULL
where CombineID In (
SELECT CombineID, count(CombineID) as test
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1 )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
remove that Count(CombineID) from the inner query

Update dbo.Name
Set CombineID = NULL
where CombineID In (
SELECT CombineID
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1 )
0
 
PeterErhardAuthor Commented:
I need that, it's critical to the update statement.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
PeterErhard,
> I need that, it's critical to the update statement.
 Can I know what you are trying to do ?  Where this is used  ?
I assume that the 'Test' value is used to update some other columns

Update a
Set CombineID = NULL
--, SomeOtherField  = t.Test
FROM dbo.Name A
INNER JOIN (
SELECT CombineID, count(CombineID) as test
from dbo.Name
GROUP By CombineID
Having count(CombineID)<=1 )T
ON A.CombineID = T.CombineID
0
 
PeterErhardAuthor Commented:
There's a field within the Name table named CombineID. If there are less or equal to 1 CombineID the same, then I want to set those CombineIDs to NULL.

and Nope, that's the full query.
0
 
PeterErhardAuthor Commented:
As an example, you could have CombineIDs like this:

1
2
4
1
6
8
5
23

Then are running the update statement it should finish up like this:

<<NULL>>
2
4
<<NULL>>
6
8
5
23
0
 
PeterErhardAuthor Commented:
Thanks, worked perfectly :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.