469,082 Members | 1,074 Online

# 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?
Jul 20 '05 #1
5 9772
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" <sp*****@mindspring.com> wrote in message
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?

Jul 20 '05 #2
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)

--
David Portas
------------
--
Jul 20 '05 #3
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
Jul 20 '05 #4
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
------------
--
Jul 20 '05 #5
> 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.
Jul 20 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.