inner join in an update

7 Sep 2006 9:01 PM

Arne Garvander

How do I update columns from one table with columns from another table?

update employees set employees.address1=employees1.address1 ,
employees.city=employees1.city,
employees.state=employees1.city,
employees.homezip= employees1.homezip
inner join employees1 on employees1.emplid = employees.emplid

--
Arne Garvander
(I program VB.Net for fun and C# to get paid.)

7 Sep 2006 9:05 PM

Aaron Bertrand [SQL Server MVP]

UPDATE e
    SET Address1 = e1.Address1,
    City = e1.City,
    State = e1.State,
    HomeZip = e1.HomeZip
FROM employees e
INNER JOIN employees1 e1
ON e.emplid = e1.emplid;

Show quote

"Arne Garvander" <ArneGarvan***@discussions.microsoft.com> wrote in message
news:6ACBBEBC-BBAF-49F5-BCBD-CC6A139F73D7@microsoft.com...
> How do I update columns from one table with columns from another table?
>
> update employees set employees.address1=employees1.address1 ,
> employees.city=employees1.city,
> employees.state=employees1.city,
> employees.homezip= employees1.homezip
> inner join employees1 on employees1.emplid = employees.emplid
> --
> Arne Garvander
> (I program VB.Net for fun and C# to get paid.)

7 Sep 2006 10:20 PM

Roy Harvey

Just for variety, a variation where the table being updated is not
included in the FROM clause:

UPDATE employees
    SET Address1 = e1.Address1,
    City = e1.City,
    State = e1.State,
    HomeZip = e1.HomeZip
FROM employees1 e1
WHERE employees.emplid = e1.emplid;

It requires the explicit table name, not an alias.

Roy Harvey
Beacon Falls, CT

On Thu, 7 Sep 2006 17:05:33 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote:

Show quote

>UPDATE e
>    SET Address1 = e1.Address1,
>    City = e1.City,
>    State = e1.State,
>    HomeZip = e1.HomeZip
>FROM employees e
>INNER JOIN employees1 e1
>ON e.emplid = e1.emplid;
>
>
>"Arne Garvander" <ArneGarvan***@discussions.microsoft.com> wrote in message
>news:6ACBBEBC-BBAF-49F5-BCBD-CC6A139F73D7@microsoft.com...
>> How do I update columns from one table with columns from another table?
>>
>> update employees set employees.address1=employees1.address1 ,
>> employees.city=employees1.city,
>> employees.state=employees1.city,
>> employees.homezip= employees1.homezip
>> inner join employees1 on employees1.emplid = employees.emplid
>> --
>> Arne Garvander
>> (I program VB.Net for fun and C# to get paid.)
>

7 Sep 2006 9:23 PM

hellmanv3

Hi Arne

Use the update from clause ie

update employees set employees.address1=employees1.address1 ,
employees.city=employees1.city,
employees.state=employees1.city,
employees.homezip= employees1.homezip

from employees employees

inner join employees1 employees1 on employees1.emplid =
employees.emplid

thanks
toby

Arne Garvander wrote:
Show quote

> How do I update columns from one table with columns from another table?
>
> update employees set employees.address1=employees1.address1 ,
> employees.city=employees1.city,
> employees.state=employees1.city,
> employees.homezip= employees1.homezip
> inner join employees1 on employees1.emplid = employees.emplid
> --
> Arne Garvander
> (I program VB.Net for fun and C# to get paid.)