don''t know how to use a cursor to insert rows from one table to another elinimating dups
-
I know this is a really common issue, but the solution escapes me
/*** What I'm trying to do here is check for the existance of a record {e.g., key violation}
while inserting records from a table that contains many duplicates.
I've also tried using a NOT EXISTS in the WHERE clause without the
LEFT OUTER JOIN, e.g., SELECT ... FROM #TEMP_WEB_NOTES - I posted this problem in the newbie
section last week and received one reply that did not use a cursor and did not work.
I realize that I must use a cursor for this operation but have no real clue as to how
to do this. So...I need to insert records from web_note_1 into temp_web_notes
where the following is true:
web_note_1 contains dups, e.g. no primary key
CC025_ORG_CODE VARCHAR(2)
CC025_EXT_ACCT_CODE VARCHAR(8)
CC025_NOTE_CLASS VARCHAR(6)
CC025_PROD_CODE VARCHAR(24)
CC025_NOTE_TEXT VARCHAR(2000)
CC025_HTML_TEXT VARCHAR(2000)
TEMP_WEB_NOTES Primary key is
CC025_ORG_CODE
CC025_EXT_ACCT_CODE
CC025_NOTE_CLASS
CC025_PROD_CODE
Tom
-
So following code will not work?
SELECT web_note_1.CC025_ORG_CODE,
web_note_1.CC025_EXT_ACCT_CODE,
web_note_1.CC025_NOTE_CLASS,
web_note_1.CC025_PROD_CODE
LEFT JOIN TEMP_WEB_NOTES ON TEMP_WEB_NOTES.CC025_ORG_CODE = web_note_1.CC025_ORG_CODE
AND TEMP_WEB_NOTES.CC025_EXT_ACCT_CODE = web_note_1.CC025_EXT_ACCT_CODE
AND TEMP_WEB_NOTES.CC025_NOTE_CLASS = web_note_1.CC025_NOTE_CLASS
AND TEMP_WEB_NOTES.CC025_PROD_CODE = web_note_1.CC025_PROD_CODE
WHERE TEMP_WEB_NOTES.CC025_ORG_CODE IS NULL
GROUP BY web_note_1.CC025_ORG_CODE,
web_note_1.CC025_EXT_ACCT_CODE,
web_note_1.CC025_NOTE_CLASS,
web_note_1.CC025_PROD_CODE
HAVING COUNT(*) > 1
Do you have some test data to show?
N 56°04'39.16"
E 12°55'05.25" -
Sample problem data
ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT
---- -------- ------ ------- --------------- ---------------
10 0000256 MEPG DECORAT One of the Bay <DIV>One of the
10 0000256 MEPG DECORAT One of the Bay <DIV>One of the
10 0000256 MEPG DECORAT One of the Bay <DIV>One of the
10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the
10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the
10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the
10 0000256 MEPG STAGING One of the Bay <DIV>One of the
10 0000256 MEPG STAGING One of the Bay <DIV>One of the
10 0000256 MEPG STAGING One of the Bay <DIV>One of the
10 0038959 WEB AVSVCS Rental of compu Rental of compu
10 0038959 WEB AVSVCS Rental of LCD/o Rental of LCD/o
10 0038959 WEB COMPREN Rental of compu Rental of compu
10 0038959 WEB COMPREN Rental of LCD/o Rental of LCD/o
Need following Output
ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT
---- -------- ------ ------- --------------- ---------------
10 0000256 MEPG DECORAT One of the Bay <DIV>One of the
10 0000256 MEPG PRODCOS One of the Bay <DIV>One of the
10 0000256 MEPG STAGING One of the Bay <DIV>One of the
10 0038959 WEB AVSVCS Rental of compu Rental of compu
10 0038959 WEB COMPREN Rental of compu Rental of compu
Where ORG, ACCT, CLASS & PROD ARE UNIQUE KEY IN NEW TABLE
and Note Text and HTML text may be the same or different, but need first instance
if dups exist.
-
>>I need to get the first occurrance of the existing note
The solution starts by identifying the business rule that defines "first".
What is the "first note" ? Is there a date/time column available to identify it ?
-
See sample data, first note, being the 'lowest value note' {in other words we don't care about which specific note text, what's important is writing a unique record where the PK is ORG, ACCT, CLASS & PROD - That's
where I'm stuck.
-
So, just arbitrary notes for each keyset ?
SELECT ORG, ACCT, CLASS, PROD,
MIN(NOTE_TEXT) AS NOTE_TEXT, MIN(HTML_TEXT) AS HTML_TEXT
FROM YourTable
GROUP BY ORG, ACCT, CLASS, PROD
-
Thanks a million, this worked! I guess it was just too simple for me to get!
-
Thanks a million; I guess the solution was just too simple for me to get
-
There is only one thing to be aware of when using this solution... MIN(NOTE_TEXT) and MIN(HTML_TEXT) are independent and the minimum value from each column is returned - which means that sometimes you could end up with a row that took NOTE from one and HTML from another of the duplicate rows. In you posted example this will not happen, but if you have duplicity like this:
ORG ACCT CLASS PROD NOTE_TEXT HTML_TEXT
---- -------- ------ ------- --------------- ---------------
10 0000256 MEPG DECORAT One of the Bay <DIV>1. One of the
10 0000256 MEPG DECORAT Bay, One of the <DIV>One of the
result will be:
10 0000256 MEPG DECORAT Bay, One of the <DIV>1. One of the
If this is not a problem, go ahead.
-
If you do need to make sure the two text columns come from the same row, you'll need to do something like:
- select V.ORG, V.ACCT, V.CLASS, V.PROD, t.NOTE_TEXT, t.HTML_TEXT
- from(
- SELECT ORG, ACCT, CLASS, PROD, MIN(KEY) KEY
FROM YourTable
GROUP BY ORG, ACCT, CLASS, PROD ) V
- join YourTable t
- on t.KEY = V.KEY
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
As I wrote yesterday?
N 56°04'39.16"
E 12°55'05.25"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply