Adding Unique Identifiers in a query

Question

Post reply

Adding Unique Identifiers in a query

PFlorenzano

SSCrazy

Points: 2180
More actions
November 13, 2003 at 7:13 am

#82547

Hello everyone,
I'm attempting to perform an update on table a by joining table b. Now, table a has bad sorce codes and table b has good source codes. Between these 2 tables, the source code is the only 'like' column.
How can I accomplish this update?
Thank you for your help.
Peter
Peter M. Florenzano

Database Administrator

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Can you post table structures and sample data with a further explanation?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

PFlorenzano SSCrazy Points: 2180 More actions · Answer 2

Frank, the following is an example of both code and result set

SELECT DISTINCT *

FROM CDSCTM_M B

WHERE NOT EXISTS

(SELECT *

FROM CPNPMO_M A

WHERE A.CPN_PMO1 = B.CTM_SRC)

This result will give me all the bad CTM_SRC's where CPN_PMO1 = CTM_SRC.

What I would like to do is Update the CDSCTM_M table with the good codes from the CPNPMO_M table.

The problem I'm running into is that there is no other joining columns between the 2 tables where I update on. The following is a sample result set:

CDSCTM_M table - Bad codes

BO8L

0C4

38562D

CPNPMO_M table - Good codes

MM300627

101393

102472

Peter M. Florenzano

Database Administrator

PFlorenzano SSCrazy Points: 2180 More actions · Answer 3

Knowone has come across this situation?

Anyone?

Thanks..:)

Peter M. Florenzano

Database Administrator

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Sorry, lost somehow track of the thread, but to be honest I have no idea...

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:
Frank, the following is an example of both code and result set
SELECT DISTINCT *
FROM CDSCTM_M B
WHERE NOT EXISTS
(SELECT *
FROM CPNPMO_M A
WHERE A.CPN_PMO1 = B.CTM_SRC)
This result will give me all the bad CTM_SRC's where CPN_PMO1 = CTM_SRC.

No, that returns the rows where CPN_PM01 != CTM_SRC. Contrary to your initial post, there is no join here, but it is equivalent to:



SELECT DISTINCT B.* 

FROM CDSCTM_M B LEFT JOIN CPNPMO_M A ON A.CPN_PMO1 = B.CTM_SRC

WHERE A.CPN_PMO1 IS NULL

quote:
What I would like to do is Update the CDSCTM_M table with the good codes from the CPNPMO_M table.
The problem I'm running into is that there is no other joining columns between the 2 tables where I update on. The following is a sample result set:
CDSCTM_M table - Bad codes
BO8L
0C4
38562D
CPNPMO_M table - Good codes
MM300627
101393
102472

As the only join is a nonequivalence, how do you know which rows from the "good" table should be used to update which rows in the "bad" table? If you can do this manually, describe how you would do it. If that's possible, then you haven't included enough information with the query you posted.

--Jonathan