SQL Server Performance

Update query

Discussion in 'SQL Server 2005 General Developer Questions' started by sonnysingh, May 2, 2007.

  1. sonnysingh Member

    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
  2. khtan New Member

    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
  3. sonnysingh Member

    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)

  4. khtan New Member

    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
  5. sonnysingh Member

    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
  6. khtan New Member


    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
  7. sonnysingh Member

    Hi Khtan

    Thanks a lot .. it works as required..

    sonny

Share This Page