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

avoiding duplicate lines

I have a oracle database that includes the following tables:

game(gameID, season, week, stadium) -- gameID is PK

competesIn(gameID, team, score) -- gameID, teamName is PK

As you can probably infer, it is a database to track the results of football games.

competesIn has two records for every game, one for each team.

I would like to create a view that combines these tables into the following structure

gameView(season, week, location, team1, score1, team2, score2)

I have tried using the following SQL inside the view:

select g.season, g.week, g.stadium,
c1.team as team1, c1.score as Score1,
c2.team as team2, c2.score as Score2
from game g, competesIn c1, competesIn c2
where c1.gameID = g.gameID
and c2.gameID = g.gameID
and c1.team != c2.team

This does create the view I was looking for but it creates two rows for each game and I need it to return only one row per game.

Any thoughts?change != in last line to <

Avoid Inserting Duplicate Entries

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

Quote:

Originally Posted by desirocks

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.|||

Quote:

Originally Posted by ck9663

i believe CONSTRAINT are still the best way to enforce your data integrity. you can also do this through trigger or check the table before insert, but i believe it'll be slower.

I AM NOT CONCERNED WITH THE PERFORMANCE... CAN YOU HELP ME WRITE THIS TRIGGER OR PROVIDE SYNTAX TO CHECK THE TABLE BEFORE THE INSERT?|||

Quote:

Originally Posted by desirocks

Hi all,

I am somewhat new to sql server. So help me in whatever way you can.

I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A.

Sometimes table A tries to insert a duplicate entry which is denied by table B since it has a primary key.

The insert query is as below.

table A - CORE
table B - CRF

INSERT INTO CRF (CORE_UID,ACCT_NUM_MIN, ACCT_NUM_MAX,BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE)
(SELECT UID,LEFT(ACCT_NUM_MIN,16),LEFT( ACCT_NUM_MAX,16),BIN, BUS_ID,BUS_NM,ISO_CTRY_CD, REGN_CD, PROD_TYPE_CD, CARD_TYPE FROM CORE WHERE UID NOT IN (SELECT CORE_UID FROM CRF))

I am not worried about the performance. I have tried if not exists and except but since i am new i am not able to figure out the problem.

Thank you in advance for all who try to help me out.

Your above INSERT QUERY should work fine. What is the problem that you are facing here??|||

Quote:

Originally Posted by amitpatel66

Your above INSERT QUERY should work fine. What is the problem that you are facing here??

Since table CRF does have a composite key it doesnt allow duplicate entries and hence my application gives me an error. I want to some how check if the entry already exists or not and based upon the same i need to insert. Is there some way i can change the query ?|||It would help to know what column(s) comprise your primary key on table B. You say that A nad B have different unique columns, but your insert statement only limits duplicates based on table A's unique key. If table B has a different unique key then that is what you need to check fo in your not in statement. Also, a NOT EXISTS should be faster, but first you need to figure out what is unique on table B.|||OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)|||

Quote:

Originally Posted by rob313

OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.CORE_UID = A.UID and B.BIN = A.BIN)

hi rob,

The composite key comprises of ACCT_NUM_MIN and ACCT_NUM_MAX. The UID of CORE is being transferred to CRF and CRF also has its own UID. Can you specify what whould be the change in the query now?|||In that case, this should work:

INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_MIN,16),
LEFT(ACCT_NUM_MAX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM CORE A
WHERE NOT EXISTS (SELECT 1 FROM CRF B WHERE B.ACCT_NUM_MIN = A.LEFT(ACCT_NUM_MIN,16) and B.ACCT_NUM_MAX = A.LEFT(ACCT_NUM_MAX,16))

Avoid duplicate primary key error

How can I avoid duplicate primary key error when I use DetailsView Inserting that the field column is one of the primary key ?

Thanks in advance !

stephen

Before insertin the new row, you can check the table to see whether there is a row in the table with the same key value as the new row. For example:

if exists (select * from Orders where OrderID=@.newOrderID)

//not insert

else //insert

|||

Can you show me the detail of correct syntax ? I'm very new in ASP.NET 2.0. I did type the statement in the ??.aspx.cs and syntax error found.

stephen

|||

Sorry I forgot to say: the code in my last post is T-SQL statements, so you can use them in SqlCommand or something else.

Avoid duplicate index while creating temp table

We are using SQL 2K with sp4.
In our stored procedure, we create a temp table (i.e. #tablename) and
various indexes within the temp table. If mutliple users execute this store
d
procedure at the same time, it would cause duplicate index error. As a
temporary fix, we attach an unique number to the index name. I like to know
what is the best way to solve this problem without extra work?
wingmanI posted a similar query yesterday ( in which I completely mispelled
temporary);
http://groups.google.co.uk/group/mi...b23b0ffd6?hl=en
the basic response was that the indexes can co-exist without naming
conflicts
Cheers
Will|||Wingman,
You can read about this in the BOL. They must be unique within a table or
view but do not need to be unique within a database. Each temporary table
(not global ones ##) is unique. SQL Server create a unique name per user.
AMB
"Wingman" wrote:

> We are using SQL 2K with sp4.
> In our stored procedure, we create a temp table (i.e. #tablename) and
> various indexes within the temp table. If mutliple users execute this sto
red
> procedure at the same time, it would cause duplicate index error. As a
> temporary fix, we attach an unique number to the index name. I like to kn
ow
> what is the best way to solve this problem without extra work?
> wingman|||dude, step back a bit, and think about a solution where you don't have
multipel users accessing the same temp table.
this has GOT to lead to integrity issues.