10.10.08

Update table with inner join with other table in sql server

I have see lots of people were querying about inner join used on update statement. Based on two tables, I need to do an update statement. Is it possible? OOPS, its possible. Let see the same through step by step.
I have created a table named employee,
create table employee(id int, name varchar(100))

Inserted some records in the table.
insert into employee values(1,'Venkat')
insert into employee values(2,'Suba')
insert into employee values(3,'Lakshmi')
insert into employee values(4,'Arun')
insert into employee values(5,'Karthi')
select * from employee

OOPs forgot to alter table employee
alter column id int not null
alter table employee add constraint prime primary key (id)
create table employeedetails(empid int, city varchar(200),designation varchar(10))
alter table employeedetails
alter column empid int not null
Am adding foreign key between these two tables,
alter table employeedetails add constraint foreignkeyforeign key (empid) references employee(id)

insert into employeedetails values(1,'Chennai','Engineer')
insert into employeedetails values(2,'Chennai','Engineer')
insert into employeedetails values(3,'Bangalore','Engineer')
insert into employeedetails values(4,'Newyork','Engineer')
select * from employeedetails

Lets see how to update the table using inner join with other table,
update a
set city ='Sydney' from employeedetails a inner join employee b on a.empid=b.id where b.[name]='Venkat'

Thanks and Regards,
Venkatesan Prabu .J

1 comment:

  1. Hi,
    I tried to get the syntax for more than half an hour and finally foune in your blog. Thanks a lot.

    ReplyDelete