LEFT JOIN

Question

Post reply

LEFT JOIN

Viewing 15 posts - 16 through 30 (of 43 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I execute the following Query:



INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)

SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID

FROM Postal_Codes_Import

WHERE NOT EXISTS (SELECT *

                  FROM Postal

				  WHERE Postal.ID = Postal_Codes_Import.ZipCode);

I get the following error:

Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).

I execute the following:



SELECT ID

FROM Postal

WHERE ID = 'B5A 5E8'

And it does not return any records.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

Welsh Corgi (1/14/2016)

I execute the following Query:

INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID) SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID FROM Postal_Codes_Import WHERE NOT EXISTS (SELECT * FROM Postal WHERE Postal.ID = Postal_Codes_Import.ZipCode);

I get the following error:

Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).

I execute the following:

SELECT ID FROM Postal WHERE ID = 'B5A 5E8'

And it does not return any records.

What does ID.Longitude refer to in the above?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

It was a typo. I removed it and tried to insert but I still get the error listed above when attempting to Execute the Insert.



INSERT INTO POSTAL (ID, Latitude, Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)

SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID

FROM Postal_Codes_Import

WHERE ZipCode NOT IN ('T0A 0A1','T0A 0A2','T8T 0W5','B5A 0B1','B5A 5A2','B5A 5A5','B5A 5A8','B5A 5B1','B5A 5B3','B5A 5B4')

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eric M Russell SSC Guru Points: 125255 More actions · Answer 4

If you want to INSERT rows for new ZipCodes and UPDATE columns for existing ZipCodes, then that's what's called an "UPSERT" or "Type 1 Change". You can implement this using a single MERGE operation.

https://www.resultdata.com/using-the-merge-statement-with-a-type-1-slowly-changing-dimension/



merge Postal

 using Postal_Codes_Import as PostImp

	on PostImp.ZipCode = Postal.ID

 when not matched then

   insert values

   (

	PostImp.ZipCode

	, PostImp.Latitude

	, PostImp.Longitude

	, PostImp.CityTownshipID

	, PostImp.CountyRegionID

	, PostImp.StateProvinceID

	, PostImp.CountryID

	)

 when matched then

   update set

	Latitude = PostImp.Latitude

	, Longitude = PostImp.Longitude

	, CityTownshipID = PostImp.CityTownshipID

	, CountyRegionID = PostImp.CountyRegionID

	, StateProvinceID = PostImp.StateProvinceID

	, CountryID = PostImp.CountryID

;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

drew.allen SSC Guru Points: 76776 More actions · Answer 5

Welsh Corgi (1/14/2016)

I execute the following:

SELECT ID FROM Postal WHERE ID = 'B5A 5E8'

And it does not return any records.

And what do you get when you execute the following code



SELECT ZipCode

FROM Postal_Codes_Import

WHERE ZipCode = 'B5A 5E8'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 6

Welsh Corgi (1/14/2016)

I execute the following Query:

INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID) SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID FROM Postal_Codes_Import WHERE NOT EXISTS (SELECT * FROM Postal WHERE Postal.ID = Postal_Codes_Import.ZipCode);

I get the following error:

Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).

I execute the following:

SELECT ID FROM Postal WHERE ID = 'B5A 5E8'

And it does not return any records.

Does this return anything?

SELECT COUNT(*), Postal_Codes_Import.ZipCode



FROM Postal_Codes_Import

WHERE NOT EXISTS (SELECT *

                  FROM Postal

		  WHERE Postal.ID = Postal_Codes_Import.ZipCode)

GROUP BY Postal_Codes_Import.ZipCode

HAVING COUNT(*)>1

If so, then you have duplicates in your import table, and you'll have to resolve that.

Cheers!

EDIT: I see Drew's trying to get you to the same place 🙂

drew.allen SSC Guru Points: 76776 More actions · Answer 7

Welsh Corgi (1/14/2016)

Yes there are 3 duplicates when I do a INNER JOIN but I exclude them in the Insert and I keep getting PK Violations. :unsure:

An INNER JOIN won't give you the situation that I'm expecting. You're looking for situations where the code does not exist in the target table and there are duplicates in the source table. An inner join will exclude those, because the code doesn't exist in the target table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Jacob Wilkins (1/14/2016)

Welsh Corgi (1/14/2016)

I execute the following Query:

INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID) SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID FROM Postal_Codes_Import WHERE NOT EXISTS (SELECT * FROM Postal WHERE Postal.ID = Postal_Codes_Import.ZipCode);

I get the following error:

Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).

I execute the following:

SELECT ID FROM Postal WHERE ID = 'B5A 5E8'

And it does not return any records.

Does this return anything?

SELECT COUNT(*), Postal_Codes_Import.ZipCode
FROM Postal_Codes_Import WHERE NOT EXISTS (SELECT * FROM Postal WHERE Postal.ID = Postal_Codes_Import.ZipCode) GROUP BY Postal_Codes_Import.ZipCode HAVING COUNT(*)>1

If so, then you have duplicates in your import table, and you'll have to resolve that.

Cheers!

EDIT: I see Drew's trying to get you to the same place 🙂

I tried the same thing and I was going to post but you beat me..

It returns 126 records however when I query the Postal Table no records are found.

I may have something to do with length of the column?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 9

It doesn't matter that there are no matching rows in the postal table.

If that query returns 126 rows, that means there are 126 ZipCodes in the Import table that occur on more than 1 row. So, even though there are no matching rows in the Postal table, if you have 2+ rows with 'B5A 5E8' in the Import table, then your current INSERTs will try to INSERT it multiple times, leading to the error you're seeing.

You'll have to remove the duplicates from the Import table, or filter out the duplicates in the INSERT.

Cheers!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

The following returns 126 records.

So the problem is that there arerror in the Import tABLE.



SELECT ZipCode , COUNT(*) AS RecordCount

FROM Postal_Codes_Import

GROUP BY  ZipCode

HAVING COUNT (*) > 1

I have to get rid of the duplicates

ZipCode RecordCount

B5A 0B1 2

B5A 5A2 2

B5A 5A5 2

B5A 5A8 3

B5A 5B1 2

B5A 5B3 2

B5A 5B4 2

B5A 5E8 2

B5A 5G1 2

B5A 5G3 3

B5A 5G5 4

B5A 5G7 2

B5A 5G8 2

B5A 5H2 2

B5A 5H3 2

B5A 5H5 2

B5A 5H8 2

B5A 5H9 2

B5A 5J1 4

B5A 5J3 2

B5A 5J4 2

B5A 5J6 2

B5A 5J7 3

B5A 5J8 2

B5A 5K2 2

B5A 5K4 2

B5A 5K5 3

B5A 5K6 2

B5A 5K7 2

B5A 5K8 2

B5A 5L1 4

B5A 5L3 2

B5A 5L5 2

B5A 5L6 3

B5A 5L7 3

B5A 5L9 2

B5A 5M7 2

B5A 5M8 2

B5A 5M9 2

B5A 5N1 2

B5A 5N3 4

B5A 5N4 2

B5A 5N6 2

B5A 5N8 2

B5A 5N9 2

B5A 5P2 3

B5A 5P8 2

B5A 5P9 2

B5A 5R1 2

B5A 5R3 3

B5A 5R5 2

B5A 5X9 3

C0A 1H1 5

C0A 1H3 5

C0A 1H6 5

C0A 1H7 4

C0A 1H9 3

C1B 0S7 2

C1B 0T9 2

C1B 0W5 2

C1B 3M8 2

C1B 3N1 2

C1B 3R7 2

C1C 0J8 2

C1C 0L4 2

C1E 0T4 2

C1E 0X5 2

C1E 2N4 2

C1E 2V5 2

C1E 2V7 2

C1E 2W9 2

E3G 7N2 2

E4S 0E8 2

G0G 0G0 2

G0N 1E1 2

G9X 0A7 2

K0E 1T1 2

K0L 1M1 2

K0M 1J2 2

K0M 2L1 2

K7S 0E1 2

K9J 0C7 2

K9J 0G6 2

L7B 0A1 2

L7B 0G1 2

L9M 0H1 2

L9Y 0R7 2

N0N 1J3 2

N0N 1J4 3

N0N 1J5 2

N3A 0A7 2

N8P 0A9 2

P0T 1C1 2

P6A 0A4 2

P6A 0A5 2

P6A 0B9 2

P7G 0S3 2

P7G 0W6 2

P7G 0W7 2

P9N 0J2 2

S0A 0L1 2

S0J 2E1 2

S0K 4T1 2

T0E 2K1 2

T0G 1L2 2

T4A 0M9 2

T4B 2T3 3

T4B 2T4 2

T4G 0G9 2

T4G 0H3 2

T4G 0J7 2

T4G 0K1 2

T4G 0K8 2

T7E 3A1 2

T7E 3E2 2

T8X 4G8 2

T8X 4L6 2

T8X 4P3 2

T8X 4R3 2

V0J 1Y2 2

V0J 1Y4 2

V0J 1Y5 2

V0K 2S1 2

V1G 0B9 2

V1G 0C2 2

V3C 0B8 2

Thanks everyone for your help.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 11

A possibly bad idea is to change an option in your PK index. Specifically IGNORE_DUP_KEY.

This will prevent errors from happening when inserting duplicates to a table, but will also hide the problems you may want to correct.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Thanks.

Does anyone have a script that could be adapted to correct the duplicate issue.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 76776 More actions · Answer 13

drew.allen

SSC Guru

Points: 76776

January 14, 2016 at 2:45 pm

#1852108

Welsh Corgi (1/14/2016)

Thanks.

Does anyone have a script that could be adapted to correct the duplicate issue.

Surely you've been around long enough to know how to set up a CTE with ROW_NUMBER to only retrieve one record per ZipCode.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I need to create a new table that has an Identity Column so that I can delete the duplicates.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323482 More actions · Answer 15

if your source has multiple lat/long for a postal code, you'd get the error;

you need to limit to a single "missing" postal code



WITH MySource

AS

(SELECT ROW_NUMBER() OVER (Partition By PostImp.ZipCode ORDER BY PostImp.ZipCode) AS RW,*

FROM Postal_Codes_Import

)

SELECT Postal.ID,MySource.ZipCode, MySource.Latitude, MySource.Longitude,MySource.CityTownshipID ,MySource.CountyRegionID, MySource.StateProvinceID, MySource.CountryID

FROM  MySource

LEFT JOIN Postal AS Postal ON MySource.ZipCode = Postal.ID

WHERE MySource.RW = 1

AND Postal.ID IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!