Update query

Hi All

I have a table with 3 columns.

Emp_rw_id Emp_id Manuf_by
1 2001 17387
7 2008 17387
8 2009 17387
10 2018 17387
3016 17387 18763
3017 17388 17387
3020 17391 17387
3042 17413 17387
3052 17423 17387
3062 17433 17387


I need to update the 'Manuf_by' column with 'emp_rw_id' column value where emp_id = Manuf_by..but update all those values exist in Manuf_by column for emp_id.
for example: 3016 emp_rw_id update Manuf_by column for 17387 values as 17387 is emp_id for Emp_rw_id 3016.

Thanks in advance

try


update t
set Manuf_by = emp_rw_id
from table t
where exists (select * from table x where x.Manuf_by = t.Emp_id)



KH

Thanks khtan

I have tried this and it will not update all the values in Manuf_by column. I want to update all the value (e.g. 17387 in Manuf_by column with 3016 Emp_rw_id)

Is this what you want ? If not, what is the required result ?


declare @table table
(
emp_rw_id int,
emp_id int,
manuf_byint
)
insert into @table
select 1, 2001, 17387 union all
select 7, 2008, 17387 union all
select 8, 2009, 17387 union all
select 10, 2018, 17387 union all
select3016, 17387, 18763 union all
select3017, 17388, 17387 union all
select3020, 17391, 17387 union all
select3042, 17413, 17387 union all
select3052, 17423, 17387 union all
select3062, 17433, 17387

update t
set manuf_by = emp_rw_id
from @table t
where exists (select * from @table x where x.manuf_by = t.emp_id)

select*
from@table
/*
emp_rw_id emp_id manuf_by
----------- ----------- -----------
1 2001 17387
7 2008 17387
8 2009 17387
10 2018 17387
3016 17387 3016
3017 17388 17387
3020 17391 17387
3042 17413 17387
3052 17423 17387
3062 17433 17387

(10 row(s) affected)
*/



KH

Thanks Khtan

Results should be like this.....

emp_rw_id emp_id manuf_by
----------- ----------- -----------
1 2001 3016
7 2008 3016
8 2009 3016
10 2018 3016
3016 17387 3016
3017 17388 3016
3020 17391 3016
3042 17413 3016
3052 17423 3016
3062 17433 3016

sonny

declare @table table
(
emp_rw_id int,
emp_id int,
manuf_byint
)
insert into @table
select 1, 2001, 17387 union all
select 7, 2008, 17387 union all
select 8, 2009, 17387 union all
select 10, 2018, 17387 union all
select3016, 17387, 18763 union all
select3017, 17388, 17387 union all
select3020, 17391, 17387 union all
select3042, 17413, 17387 union all
select3052, 17423, 17387 union all
select3062, 17433, 17387

updatet
setmanuf_by= a.emp_rw_id
from@table t inner join
(
selecta.emp_rw_id, a.emp_id
from @table t inner join @table a
ont.manuf_by= a.emp_id
group by a.emp_rw_id, a.emp_id
) a
ont.manuf_by= a.emp_id
ort.emp_rw_id= a.emp_rw_id

select* from @table

/*

emp_rw_id emp_id manuf_by
----------- ----------- -----------
1 2001 3016
7 2008 3016
8 2009 3016
10 2018 3016
3016 17387 3016
3017 17388 3016
3020 17391 3016
3042 17413 3016
3052 17423 3016
3062 17433 3016
*/



KH

Hi Khtan

Thanks a lot .. it works as required..

sonny