[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1946
  • Last Modified:

The UPDATE statement conflicted with the FOREIGN KEY

Ive got an issue updating a field in an MS CRM (SQL) database

Basically i have a load of accounts with no primary contact, however, there are multiple contacts for each account....

Update dbo.accountbase
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]
From dbo.accountbase
Inner Join [contactbase]
on dbo.accountbase.[accountid] = dbo.contactbase.[accountid]
where accountbase.[primarycontactid] is null and dbo.contactbase.fullname is not null and dbo.accountbase.deletionstatecode = 0

Gives me....
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "account_primary_contact". The conflict occurred in database "CRM_MSCRM", table "dbo.ContactBase", column 'ContactId'.
The statement has been terminated.

I just want to pick the first contact availible, how can i modify this query to update the account with only the first contact?

Thanks
0
Dave_Angel_Portsmouth
Asked:
Dave_Angel_Portsmouth
  • 5
  • 4
1 Solution
 
SStoryCommented:
I would try to Inner join on a subquery that gets the top 1 contact for each account number
0
 
Dave_Angel_PortsmouthAuthor Commented:
Thanks SStory, how would i do that?
0
 
SStoryCommented:

OK wait just a minute.

You are setting the contactid = accountid

Is that correct?

Are contactID just account ID's.  It would seem to me that an account would have more than one contact which is what you said and that this wouldn't be correct.  Please let me know.  Also the table structure would help as I don't have whatever product you are using.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Dave_Angel_PortsmouthAuthor Commented:
Yes thats right.

The contacts have a one to many relationship with accounts, but there can only be one primary contact for the account.

So i am just trying to populate the primary contact with any of the availible contact ids for that account
0
 
Dave_Angel_PortsmouthAuthor Commented:
here is the contactbase table...
USE [NxteraCRM_MSCRM]
GO
/****** Object:  Table [dbo].[ContactBase]    Script Date: 05/01/2009 15:08:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContactBase](
	[ContactId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[DefaultPriceLevelId] [uniqueidentifier] NULL,
	[CustomerSizeCode] [int] NULL,
	[CustomerTypeCode] [int] NULL,
	[PreferredContactMethodCode] [int] NULL,
	[LeadSourceCode] [int] NULL,
	[DeletionStateCode] [int] NOT NULL,
	[OriginatingLeadId] [uniqueidentifier] NULL,
	[OwningBusinessUnit] [uniqueidentifier] NULL,
	[OwningUser] [uniqueidentifier] NULL,
	[PaymentTermsCode] [int] NULL,
	[ShippingMethodCode] [int] NULL,
	[OwningTeam] [uniqueidentifier] NULL,
	[AccountId] [uniqueidentifier] NULL,
	[ParticipatesInWorkflow] [bit] NULL CONSTRAINT [Set_To_Zero103]  DEFAULT ((0)),
	[IsBackofficeCustomer] [bit] NULL,
	[Salutation] [nvarchar](100) NULL,
	[JobTitle] [nvarchar](100) NULL,
	[FirstName] [nvarchar](50) NULL,
	[Department] [nvarchar](100) NULL,
	[NickName] [nvarchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[Suffix] [nvarchar](10) NULL,
	[YomiFirstName] [nvarchar](150) NULL,
	[FullName] [nvarchar](160) NULL,
	[YomiMiddleName] [nvarchar](150) NULL,
	[YomiLastName] [nvarchar](150) NULL,
	[Anniversary] [datetime] NULL,
	[BirthDate] [datetime] NULL,
	[GovernmentId] [nvarchar](50) NULL,
	[YomiFullName] [nvarchar](450) NULL,
	[Description] [nvarchar](max) NULL,
	[EmployeeId] [nvarchar](50) NULL,
	[GenderCode] [int] NULL,
	[AnnualIncome] [money] NULL,
	[HasChildrenCode] [int] NULL,
	[EducationCode] [int] NULL,
	[WebSiteUrl] [nvarchar](200) NULL,
	[FamilyStatusCode] [int] NULL,
	[FtpSiteUrl] [nvarchar](200) NULL,
	[EMailAddress1] [nvarchar](100) NULL,
	[SpousesName] [nvarchar](100) NULL,
	[AssistantName] [nvarchar](100) NULL,
	[EMailAddress2] [nvarchar](100) NULL,
	[AssistantPhone] [nvarchar](50) NULL,
	[EMailAddress3] [nvarchar](100) NULL,
	[DoNotPhone] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotPhone]  DEFAULT ((0)),
	[ManagerName] [nvarchar](100) NULL,
	[ManagerPhone] [nvarchar](50) NULL,
	[DoNotFax] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotFax]  DEFAULT ((0)),
	[DoNotEMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotEMail]  DEFAULT ((0)),
	[DoNotPostalMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotPostalMail]  DEFAULT ((0)),
	[DoNotBulkEMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotBulkEMail]  DEFAULT ((0)),
	[DoNotBulkPostalMail] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotBulkPostalMail]  DEFAULT ((0)),
	[AccountRoleCode] [int] NULL,
	[TerritoryCode] [int] NULL,
	[IsPrivate] [bit] NULL CONSTRAINT [Set_To_Zero104]  DEFAULT ((0)),
	[CreditLimit] [money] NULL,
	[CreatedOn] [datetime] NULL,
	[CreditOnHold] [bit] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[NumberOfChildren] [int] NULL,
	[ChildrensNames] [nvarchar](255) NULL,
	[VersionNumber] [timestamp] NULL,
	[MobilePhone] [nvarchar](50) NULL,
	[Pager] [nvarchar](50) NULL,
	[Telephone1] [nvarchar](50) NULL,
	[Telephone2] [nvarchar](50) NULL,
	[Telephone3] [nvarchar](50) NULL,
	[Fax] [nvarchar](50) NULL,
	[Aging30] [money] NULL,
	[StateCode] [int] NOT NULL,
	[Aging60] [money] NULL,
	[StatusCode] [int] NULL,
	[Aging90] [money] NULL,
	[ParentContactId] [uniqueidentifier] NULL,
	[PreferredSystemUserId] [uniqueidentifier] NULL,
	[PreferredServiceId] [uniqueidentifier] NULL,
	[MasterId] [uniqueidentifier] NULL,
	[PreferredAppointmentDayCode] [int] NULL,
	[PreferredAppointmentTimeCode] [int] NULL,
	[DoNotSendMM] [bit] NULL CONSTRAINT [DF_ContactBase_DoNotSendMM]  DEFAULT ((0)),
	[Merged] [bit] NULL CONSTRAINT [DF_ContactBase_Merged]  DEFAULT ((0)),
	[ExternalUserIdentifier] [nvarchar](50) NULL,
	[SubscriptionId] [uniqueidentifier] NULL,
	[PreferredEquipmentId] [uniqueidentifier] NULL,
	[LastUsedInCampaign] [datetime] NULL,
	[TransactionCurrencyId] [uniqueidentifier] NULL,
	[OverriddenCreatedOn] [datetime] NULL,
	[ExchangeRate] [decimal](23, 10) NULL,
	[ImportSequenceNumber] [int] NULL,
	[TimeZoneRuleVersionNumber] [int] NULL,
	[UTCConversionTimeZoneCode] [int] NULL,
	[AnnualIncome_Base] [money] NULL,
	[CreditLimit_Base] [money] NULL,
	[Aging60_Base] [money] NULL,
	[Aging90_Base] [money] NULL,
	[Aging30_Base] [money] NULL,
 CONSTRAINT [cndx_PrimaryKey_Contact] PRIMARY KEY CLUSTERED 
(
	[ContactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [account_contacts] FOREIGN KEY([AccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [account_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [business_unit_contacts] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [business_unit_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_master_contact] FOREIGN KEY([MasterId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_master_contact]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_originating_lead] FOREIGN KEY([OriginatingLeadId])
REFERENCES [dbo].[LeadBase] ([LeadId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_originating_lead]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_owning_user] FOREIGN KEY([OwningUser])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_owning_user]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [contact_parent_contact] FOREIGN KEY([ParentContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [contact_parent_contact]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [equipment_contacts] FOREIGN KEY([PreferredEquipmentId])
REFERENCES [dbo].[EquipmentBase] ([EquipmentId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [equipment_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [price_level_contacts] FOREIGN KEY([DefaultPriceLevelId])
REFERENCES [dbo].[PriceLevelBase] ([PriceLevelId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [price_level_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [service_contacts] FOREIGN KEY([PreferredServiceId])
REFERENCES [dbo].[ServiceBase] ([ServiceId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [service_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [system_user_contacts] FOREIGN KEY([PreferredSystemUserId])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [system_user_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [team_contacts] FOREIGN KEY([OwningTeam])
REFERENCES [dbo].[TeamBase] ([TeamId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [team_contacts]
GO
ALTER TABLE [dbo].[ContactBase]  WITH NOCHECK ADD  CONSTRAINT [transactioncurrency_contact] FOREIGN KEY([TransactionCurrencyId])
REFERENCES [dbo].[TransactionCurrencyBase] ([TransactionCurrencyId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[ContactBase] CHECK CONSTRAINT [transactioncurrency_contact]

Open in new window

0
 
Dave_Angel_PortsmouthAuthor Commented:
Here is the accountbase table...
USE [NxteraCRM_MSCRM]
GO
/****** Object:  Table [dbo].[AccountBase]    Script Date: 05/01/2009 15:09:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccountBase](
	[AccountId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[AccountCategoryCode] [int] NULL,
	[TerritoryId] [uniqueidentifier] NULL,
	[DefaultPriceLevelId] [uniqueidentifier] NULL,
	[CustomerSizeCode] [int] NULL,
	[PreferredContactMethodCode] [int] NULL,
	[CustomerTypeCode] [int] NULL,
	[AccountRatingCode] [int] NULL,
	[IndustryCode] [int] NULL,
	[TerritoryCode] [int] NULL,
	[AccountClassificationCode] [int] NULL,
	[DeletionStateCode] [int] NOT NULL,
	[BusinessTypeCode] [int] NULL,
	[OwningBusinessUnit] [uniqueidentifier] NULL,
	[OwningTeam] [uniqueidentifier] NULL,
	[OwningUser] [uniqueidentifier] NULL,
	[OriginatingLeadId] [uniqueidentifier] NULL,
	[PaymentTermsCode] [int] NULL,
	[ShippingMethodCode] [int] NULL,
	[PrimaryContactId] [uniqueidentifier] NULL,
	[ParticipatesInWorkflow] [bit] NULL CONSTRAINT [Set_To_Zero93]  DEFAULT ((0)),
	[Name] [nvarchar](160) NULL,
	[AccountNumber] [nvarchar](20) NULL,
	[Revenue] [money] NULL,
	[NumberOfEmployees] [int] NULL,
	[Description] [nvarchar](max) NULL,
	[SIC] [nvarchar](20) NULL,
	[OwnershipCode] [int] NULL,
	[MarketCap] [money] NULL,
	[SharesOutstanding] [int] NULL,
	[TickerSymbol] [nvarchar](10) NULL,
	[StockExchange] [nvarchar](20) NULL,
	[WebSiteURL] [nvarchar](200) NULL,
	[FtpSiteURL] [nvarchar](200) NULL,
	[EMailAddress1] [nvarchar](100) NULL,
	[EMailAddress2] [nvarchar](100) NULL,
	[EMailAddress3] [nvarchar](100) NULL,
	[DoNotPhone] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotPhone]  DEFAULT ((0)),
	[DoNotFax] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotFax]  DEFAULT ((0)),
	[Telephone1] [nvarchar](50) NULL,
	[DoNotEMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotEMail]  DEFAULT ((0)),
	[Telephone2] [nvarchar](50) NULL,
	[Fax] [nvarchar](50) NULL,
	[Telephone3] [nvarchar](50) NULL,
	[DoNotPostalMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotPostalMail]  DEFAULT ((0)),
	[DoNotBulkEMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotBulkEMail]  DEFAULT ((0)),
	[DoNotBulkPostalMail] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotBulkPostalMail]  DEFAULT ((0)),
	[CreditLimit] [money] NULL,
	[CreditOnHold] [bit] NULL,
	[IsPrivate] [bit] NULL CONSTRAINT [Set_To_Zero94]  DEFAULT ((0)),
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[VersionNumber] [timestamp] NULL,
	[ParentAccountId] [uniqueidentifier] NULL,
	[Aging30] [money] NULL,
	[StateCode] [int] NOT NULL,
	[Aging60] [money] NULL,
	[StatusCode] [int] NULL,
	[Aging90] [money] NULL,
	[PreferredAppointmentDayCode] [int] NULL,
	[PreferredSystemUserId] [uniqueidentifier] NULL,
	[PreferredAppointmentTimeCode] [int] NULL,
	[Merged] [bit] NULL CONSTRAINT [DF_AccountBase_Merged]  DEFAULT ((0)),
	[DoNotSendMM] [bit] NULL CONSTRAINT [DF_AccountBase_DoNotSendMM]  DEFAULT ((0)),
	[MasterId] [uniqueidentifier] NULL,
	[LastUsedInCampaign] [datetime] NULL,
	[PreferredServiceId] [uniqueidentifier] NULL,
	[PreferredEquipmentId] [uniqueidentifier] NULL,
	[ExchangeRate] [decimal](23, 10) NULL,
	[UTCConversionTimeZoneCode] [int] NULL,
	[OverriddenCreatedOn] [datetime] NULL,
	[TimeZoneRuleVersionNumber] [int] NULL,
	[ImportSequenceNumber] [int] NULL,
	[TransactionCurrencyId] [uniqueidentifier] NULL,
	[CreditLimit_Base] [money] NULL,
	[Aging30_Base] [money] NULL,
	[Revenue_Base] [money] NULL,
	[Aging90_Base] [money] NULL,
	[MarketCap_Base] [money] NULL,
	[Aging60_Base] [money] NULL,
	[YomiName] [nvarchar](160) NULL,
 CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED 
(
	[AccountId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_master_account] FOREIGN KEY([MasterId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_master_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_originating_lead] FOREIGN KEY([OriginatingLeadId])
REFERENCES [dbo].[LeadBase] ([LeadId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_originating_lead]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_parent_account] FOREIGN KEY([ParentAccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_parent_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [account_primary_contact] FOREIGN KEY([PrimaryContactId])
REFERENCES [dbo].[ContactBase] ([ContactId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [account_primary_contact]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [business_unit_accounts] FOREIGN KEY([OwningBusinessUnit])
REFERENCES [dbo].[BusinessUnitBase] ([BusinessUnitId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [business_unit_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [equipment_accounts] FOREIGN KEY([PreferredEquipmentId])
REFERENCES [dbo].[EquipmentBase] ([EquipmentId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [equipment_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [price_level_accounts] FOREIGN KEY([DefaultPriceLevelId])
REFERENCES [dbo].[PriceLevelBase] ([PriceLevelId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [price_level_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [service_accounts] FOREIGN KEY([PreferredServiceId])
REFERENCES [dbo].[ServiceBase] ([ServiceId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [service_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [system_user_accounts] FOREIGN KEY([PreferredSystemUserId])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [system_user_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [team_accounts] FOREIGN KEY([OwningTeam])
REFERENCES [dbo].[TeamBase] ([TeamId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [team_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [territory_accounts] FOREIGN KEY([TerritoryId])
REFERENCES [dbo].[TerritoryBase] ([TerritoryId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [territory_accounts]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [transactioncurrency_account] FOREIGN KEY([TransactionCurrencyId])
REFERENCES [dbo].[TransactionCurrencyBase] ([TransactionCurrencyId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [transactioncurrency_account]
GO
ALTER TABLE [dbo].[AccountBase]  WITH NOCHECK ADD  CONSTRAINT [user_accounts] FOREIGN KEY([OwningUser])
REFERENCES [dbo].[SystemUserBase] ([SystemUserId])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[AccountBase] CHECK CONSTRAINT [user_accounts]

Open in new window

0
 
SStoryCommented:
Well, what I am saying is assigning an accountid to a contactid seemed wrong.
0
 
SStoryCommented:
According to the defs you have presented it is wrong.

Accountbase and Contactbase both have an AccountID field

the PK of Accountbase appears to be AccountID
the PK of contactBase is probably ContactID

The FK from contactBase to the PK of AccountBase would be the ContactBase.AccountID

You had:
Set accountbase.[primarycontactid] = dbo.contactbase.[accountid]

This is incorrect -- apples to oranges

This would be apples to apples:
Set accountbase.[primarycontactid] = dbo.contactbase.[contactid]
0
 
SStoryCommented:
Update accountbase a
INNER JOIN contactbase c
ON a.accountid=c.accountid
set a.primarycontactid = c.accountid
where (a.primarycontactid is null)
   AND (c.fullname is not null)
   AND (a.deletionstatecode = 0)


This is closer, but not right.
I am reading, and it appears that maybe the ROW_NUMBER with PARTITION may do this:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now