Insert using a join

Question

Post reply

Insert using a join

ubeauty

Ten Centuries

Points: 1034
More actions
April 3, 2009 at 2:59 am

#201779

Dear all
I have a field called Has Email Address which is either y or n (the y option is ExtraValueID 98203)
I'm trying to update any records in a table called ExtraContact that have an email address which is held in a table called Contact.
INSERT INTO ExtraContact
SELECT 98203, E.ContactID
FROM ExtraContact E INNER JOIN Contact C ON E.ContactID = C.ContactID
WHERE Contact.Email IS NOT NULL
AND NOT EXISTS (SELECT * FROM ExtraContact WHERE ContactID = E.ContactID AND ExtraValueID = 98203 )
I receive the following constraint error, i'm struggling with the logic of the insert statement using the value from another table without violating the primary key constraint .This is a new field and no information has been updated yet, and i thought the AND NOT EXISTS would deal with the constraint
Msg 2627, Level 14, State 1, Line 30
Violation of PRIMARY KEY constraint 'PK_ExtraContact'. Cannot insert duplicate key in object 'dbo.ExtraContact'.
The statement has been terminated.
Any help or guidance would be much appreciated
Thanks in advance
Karl

Viewing 12 posts - 1 through 11 (of 11 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

Can you please post the table create script for table ExtraContact?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 2

/****** Object: Table [dbo].[ExtraContact] Script Date: 04/03/2009 10:14:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ExtraContact](

[ExtraValueID] [int] NOT NULL,

[ContactID] [int] NOT NULL,

CONSTRAINT [PK_ExtraContact] PRIMARY KEY CLUSTERED

(

[ContactID] ASC,

[ExtraValueID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ExtraContact] WITH CHECK ADD CONSTRAINT [FK_ExtraContact_Contact] FOREIGN KEY([ContactID])

REFERENCES [dbo].[Contact] ([ContactID])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[ExtraContact] CHECK CONSTRAINT [FK_ExtraContact_Contact]

GO

ALTER TABLE [dbo].[ExtraContact] WITH CHECK ADD CONSTRAINT [FK_ExtraContact_ExtraValue] FOREIGN KEY([ExtraValueID])

REFERENCES [dbo].[ExtraValue] ([ExtraValueID])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[ExtraContact] CHECK CONSTRAINT [FK_ExtraContact_ExtraValue]

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

As Chris already wrote it would help a lot if you posted the schema.

But one thing I noticed is you write, you want to update records, but you execute an INSERT.

[font="Verdana"]Markus Bohse[/font]

ubeauty Ten Centuries Points: 1034 More actions · Answer 4

Wrong use of words sorry, the ExtraContact table holds the information about the Contact records so i need to insert the information into there

Apologies for the confusion

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

What does this return, Karl?

SELECT e.ExtraValueID, e.ContactID

FROM ExtraContact e

INNER JOIN Contact c ON c.ContactID = e.ContactID AND c.Email IS NOT NULL

WHERE e.ExtraValueID = 98203

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 6

Apparently somebody has been updating the field, wouldn't the AND NOT EXISTS take care of this?

ExtraValueIDContactID

982037838

982037854

982038392

9820310162

9820310185

9820310238

9820310887

9820312466

9820312568

9820313579

9820314572

9820344282

9820344946

9820345919

9820346268

9820346645

9820347667

9820348318

9820348377

9820348414

9820348416

9820348417

9820348638

9820348690

9820348708

9820348710

9820348809

9820348992

9820349767

9820349965

9820350217

9820350222

9820350414

9820350653

9820350763

9820350787

9820350845

9820351221

9820351514

9820351541

9820351559

9820351775

9820351921

9820352308

9820352540

9820352673

9820352771

9820352772

9820352879

9820352880

9820354934

9820354935

9820355089

9820355482

9820355511

9820355606

9820355736

9820356306

9820356992

9820356994

9820357188

9820358035

9820358036

9820358098

9820363681

9820366761

9820367954

9820368893

9820369227

9820370627

9820373038

9820373039

9820373040

9820373355

9820373426

9820374299

9820374572

9820374574

9820374708

9820374720

9820374928

9820375030

9820375329

9820375559

9820375617

9820376899

9820378351

9820378847

9820379868

9820380161

9820380302

9820380435

9820380501

9820380740

9820380777

9820381135

9820381182

9820381427

9820384255

9820384866

9820384868

9820384873

9820385012

9820385379

9820386126

9820386183

9820386634

9820386669

9820386695

9820386820

9820386821

9820386948

9820387617

9820387727

9820387836

9820387954

9820388034

9820388163

9820388173

9820388266

9820388445

9820388508

ubeauty Ten Centuries Points: 1034 More actions · Answer 7

ubeauty

Ten Centuries

Points: 1034

April 3, 2009 at 3:44 am

#971388

122 rows affected btw

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

I don't think the original query does what you think it should. Try this, but comment out the INSERT line first and run only the SELECT:

INSERT INTO ExtraContact (ExtraValueID, ContactID)

SELECT 98203 AS ExtraValueID, c.ContactID

FROM Contact c

LEFT JOIN ExtraContact e ON e.ContactID = c.ContactID AND e.ExtraValueID = 98203

WHERE c.Email IS NOT NULL AND e.ContactID IS NULL

This says, in English: get rows from the Contact table which have something in their Email column. Match them on ContactID to the ExtraContact table filtered on 98203 AS ExtraValueID. Where there's no match, insert them into the ExtraContact table.

Notice that there's a column list in the INSERT - how do you know your columns weren't getting swapped?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 9

Thanks for the heads up Chris, i ran your script with the Insert commented out which returned 10871 rows if you add this to the 122 records this totals 10993, i've just run a count of the number of records that have an email address which came to 10993! Brilliant!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Nice work Karl, Happy Friday!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 11

Thanks again Chris, have yourself a great weekend 😀