Как я могу вставить набор дочерних записей при обновлении родителя?

Я использую SQL Server 2005 и хочу создать записи с номерами адресов, обновив записи контактов новыми идентификаторами:

Возьмите следующие таблицы

create table contact(id int primary key identity, home_address_id int, work_address_id int)

create table address(id int primary key identity, street varchar(25), number int)

И внешние ключи:

ALTER TABLE dbo.contact ADD CONSTRAINT  FK_contact_address1 FOREIGN KEY (home_address_id) REFERENCES dbo.address(id)
ALTER TABLE dbo.contact ADD CONSTRAINT  FK_contact_address2 FOREIGN KEY (work_address_id) REFERENCES dbo.address(id)

какие-то фиктивные данные

insert into contact default values
insert into contact default values
insert into contact default values

Как я могу вставить пустую запись адреса по умолчанию для всех контактов, у которых нет домашнего адреса, и обновить home_address_id за один раз?

Первая часть проста:

insert into address(street) select null from contact where home_address_id is null

Я даже могу получить только что созданный идентификатор адреса:

declare @addressTable table(id int)
insert into address(street) 
OUTPUT INSERTED.Id INTO @addressTable
select null from contact where home_address_id is null

Вот новый идентификатор

select * from @addressTable

Но как обновить таблицу контактов с этими новыми идентификаторами?


sql sql-update parent sql-server-2005
person edosoft    schedule 29.07.2010    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Если возможно, я бы предложил нормализовать вашу базу данных, добавив таблицу Contact_Addresses:

CREATE TABLE Contact_Addresses
(
    contact_id INT NOT NULL,
    address_id INT NOT NULL,
    address_type VARCHAR(10) NOT NULL,
    CONSTRAINT PK_Contact_Addresses PRIMARY KEY CLUSTERED (contact_id, address_id, address_type),
    CONSTRAINT FK_ContactAddresses_Contacts (contact_id) REFERENCES Contacts (id),
    CONSTRAINT FK_ContactAddresses_Addresses (address_id) REFERENCES Addresses (id),
    CONSTRAINT CK_ContactAddresses_address_type CHECK address_type IN ('HOME', 'WORK')
)

Далее, я бы посоветовал не помещать в вашу базу данных "фиктивные" записи. Это в конечном итоге вызовет головную боль в будущем. База данных должна содержать точную запись данных в вашей системе. Если вы хотите отображать какое-то значение по умолчанию, когда в системе нет адреса для контакта, обработайте это в своем пользовательском интерфейсе.

Если вам действительно нужно, то следующий код должен помочь:

;WITH C_CTE AS
(
    SELECT
        id,
        home_address_id,
        ROW_NUMBER() OVER(ORDER BY id) AS seq
    FROM
        Contacts
),
(
    SELECT
        id,
        ROW_NUMBER() OVER(ORDER BY id) AS seq
    FROM
        Addresses
)
UPDATE
    C_CTE
SET
    home_address_id = A.id
FROM
    C_CTE C
INNER JOIN A_CTE A ON A.seq = C.seq
person Tom H    schedule 29.07.2010

arrow_upward
1
arrow_downward

Я бы сделал это с того момента, как вы получите новый контакт, таким образом:

[receive contact information]
//prior to inserting contact
declare @homeAddress int, @workAddress int

[insert home address here (real or default based on input)]
set @homeAddress = @@Identity

[insert work address here (real or default)]
set @workAddress = @@Identity

[insert contact here referencing @homeAddress & @workAddress]

Для вещей, которые уже есть в вашей таблице, вам придется связать все ваши идентификаторы с нулевым значением с идентификатором контакта. Или вы можете очистить свои нулевые адреса и каким-то образом изменить приведенное выше утверждение для обновления (мозг в данный момент не работает, поэтому все, что я придумываю, это курсор, а курсоры - зло).

person AllenG    schedule 29.07.2010