471,873 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

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 9819
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
news:67**************************@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?

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)


Please don't multi-post.

--
David Portas
------------
Please reply only to the newsgroup
--
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
------------
Please reply only to the newsgroup
--
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.

Similar topics

7 posts views Thread by Dave | last post: by
17 posts views Thread by kalamos | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.