Avoiding temp tables

Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:

>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan