count and update syntax help

Field Names: NOs Code Code1a UniqueID
61 10 888 10
62 10 888 11
63 10 888 12

Logic: If Count(code >1) & Count (Code1a >1)
Update the (Nos) to EQUAL the same Value.
ALL the Nos for the above examble should be the same value for
all three records whether it's 61 for all three records of any
of the other two numbers, it doesn't matter as long as the equal the same value.
How can this be done via sql?Hi!
I' didn't really understood what you mean, but I'm sure you can use:

select code from tabx group by code having count(*)>1
select min(NOs) from tabx where ....
/Bjrn

"Spencer" <spencey@.mindspring.com> wrote in message
news:673a4d41.0311120525.3203e609@.posting.google.c om...
> Field Names: NOs Code Code1a
UniqueID
> 61 10 888
10
> 62 10 888
11
> 63 10 888
12
> Logic: If Count(code >1) & Count (Code1a >1)
> Update the (Nos) to EQUAL the same Value.
> ALL the Nos for the above examble should be the same value for
> all three records whether it's 61 for all three records of any
> of the other two numbers, it doesn't matter as long as the equal the same
value.
> How can this be done via sql?|||Replied in microsoft.public.sqlserver.programming:

> UPDATE Sometable
> SET nos =
> (SELECT MIN(nos)
> FROM Sometable AS S
> WHERE S.code = Sometable.code
> AND S.code1a = Sometable.code1a)

Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi Spencer,

Here's one way of doing it using UPDATE FROM. - Louis

create table #T (n int, codeA int, codeB int, id uniqueidentifier)
insert into #T values(11,1,888,newid())
insert into #T values(12,1,888,newid())
insert into #T values(13,1,888,newid())
insert into #T values(21,10,888,newid())
insert into #T values(22,10,888,newid())
insert into #T values(23,10,888,newid())
insert into #T values(1,1,111,newid())
insert into #T values(2,2,222,newid())
insert into #T values(3,3,333,newid())
insert into #T values(3,4,444,newid())

select codeA,codeB,n=min(n)
into #U
from #T
group by codeA,codeB
having count(*)>1

update #T
set n=b.n
from #T as a
JOIN #U as b
ON a.codeA=b.codeA and a.codeB=b.codeB

select n,codeA,codeB from #T

returns:
n codeA codeB
---- ---- ----
11 1 888
11 1 888
11 1 888
21 10 888
21 10 888
21 10 888
1 1 111
2 2 222
3 3 333
3 4 444|||Wow! Do you have something against using an UPDATE subquery?

It may be worth adding a WHERE clause to my original suggestion in line with
the HAVING COUNT(*)>1 requirement.

UPDATE Sometable
SET nos =
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)
WHERE nos >
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1a)

Assuming Nos is not nullable.

--
David Portas
----
Please reply only to the newsgroup
--|||> Wow! Do you have something against using an UPDATE subquery?
Hi David,

My little brain can't handle subqueries ;) I'm currently undergoing
brain overload, trying to figure out how to use W3C SVG to create
dynamic charts on the web.