TSQL inter-table updates

Question

I have two tables, a contacts table and an addresses table. The addresses table contains a contact id, and 4 address lines. I wish to update the contacts table with the information from the addresses table. For simplicity's sake let the tables be as follows:

addresses(
. contact int not null,
. address1 varchar(32) not null,
. address2 varchar(32) not null
)

contacts(
. id int primary key,
. addr1 varchar(32) not null,
. addr2 varchar(32) not null
)

How (int tsql) do I update the contacts table from the addresses table?
Thanks.

Answer 1

You don't. Your schema is broken. You are repeating the address data in more than one place.

Your schema should be something like

addresses( . id int primary key, . address1 varchar(32) not null, . address2 varchar(32) not null )

contacts( .id int primary key .addressId int foreign key (addresses.id) }

So to get a contact's address, you would perform a join as follows:

select 
    c.id contactId,
    a.address1,
    a.address2 
from 
    contacts c 
    join addresses a on 
        c.addressId=a.id 
where 
    c.id=@someContactId

and to update you would perform the following:

update a
   set a.address1='27 Foo Street'
from
   addresses as a
   join contacts as c
       on a.id=c.addressId
where c.id=@someContactId

It's rarely a good idea to duplicate data in a database. Search around for the term database normalization for more info on this subject.

Answer 2

I agree with 'spender' - you shouldn't be duplicating this data.

If you must, however, do a batch update, this is the T-SQL statement you'd use:

UPDATE dbo.Contacts
SET addr1 = address1,
    addr2 = address2
FROM dbo.Addresses
WHERE id = contact

You just join the Contacts table to the Addresses table by defining the WHERE id = contact clause and then you set the addr1/addr2 fields on the Contacts table to the corresponding values of address1/address2 on the Addresses table - pretty simple, huh?

Marc