count and update syntax help

[Spencer]

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?

[bdjensen]

Re: count and update syntax help

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 .....
/Bjørn



"Spencer" <spencey@mindsp ring.com> wrote in message
news:673a4d41.0 311120525.3203e 609@posting.goo gle.com...[color=blue]
> Field Names: NOs Code Code1a[/color]
UniqueID[color=blue]
> 61 10 888[/color]
10[color=blue]
> 62 10 888[/color]
11[color=blue]
> 63 10 888[/color]
12[color=blue]
>
> 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[/color]
value.[color=blue]
> How can this be done via sql?[/color]

[David Portas]

Re: count and update syntax help

Replied in microsoft.publi c.sqlserver.pro gramming:
[color=blue]
> UPDATE Sometable
> SET nos =
> (SELECT MIN(nos)
> FROM Sometable AS S
> WHERE S.code = Sometable.code
> AND S.code1a = Sometable.code1 a)[/color]

Please don't multi-post.

--
David Portas
------------
Please reply only to the newsgroup
--

[louis nguyen]

Re: count and update syntax help

Hi Spencer,

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

create table #T (n int, codeA int, codeB int, id uniqueidentifie r)
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,88 8,newid())
insert into #T values(22,10,88 8,newid())
insert into #T values(23,10,88 8,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=m in(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

[David Portas]

Re: count and update syntax help

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.code1 a)
WHERE nos >
(SELECT MIN(nos)
FROM Sometable AS S
WHERE S.code = Sometable.code
AND S.code1a = Sometable.code1 a)

Assuming Nos is not nullable.

--
David Portas
------------
Please reply only to the newsgroup
--

[louis nguyen]

Re: count and update syntax help

> 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.