Updating more than one rows at a time

  • Hi All,

    My requirement is like,

    I have one table in that table I want to change col1's values and

    these values are different for every employee, so currently I have

    written Update query with case Statement like :

    Update Table1 set Col1=(

    case when Employeeid=123 then 'abc1'

    case when Employeeid=124 then 'abc2'

    case when Employeeid=125 then 'abc3'

    Else Col1

    End)

    This query is working fine but when we consider the case that we have

    more than 20,000 rows then how it will behave as we are checking row

    by row for each employee.

    Does anybody knows any other way to do this then please reply soon.

    Thanks,

    Rohit.

  • Step1: Create Table2 with columns Employeeid, Col1

    Step2: insert into Table 2 values (123, 'abc1')

    insert into Table 2 values (124, 'abc2')

    insert into Table 2 values (125, 'abc3')

    and so on ....

    Step3:

    Update Table1 set Col1 = B.Col1

    from Table1 A, Table2 B

    where A.Employeeid = B.Employeeid

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • Your example makes it look like the Col1 value might be calculated as a function of EmployeeId. Is this true?

  • You can have functioin by passing the EmployeeId and get the required data.

    The join provided above is called bulk update where multiple records can be updated as single statement. The query can be re-written using SQL Server joins

    update e1

    set e1.col = e2.col

    from Employee e1

    inner join Employee e2 on e1.EmployeeID = e2.EmployeeID

    Regards - JL

  • In fact what I used is traditional join which is supported in MS SQL Server also while the one provided by Jitendra is ANSI Join which is used in other databases also other than SQL Server.

    http://www.databasejournal.com/features/mssql/article.php/1438001

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • Rohit (9/8/2008)


    Hi All,

    My requirement is like,

    I have one table in that table I want to change col1's values and

    these values are different for every employee, so currently I have

    written Update query with case Statement like :

    Update Table1 set Col1=(

    case when Employeeid=123 then 'abc1'

    case when Employeeid=124 then 'abc2'

    case when Employeeid=125 then 'abc3'

    Else Col1

    End)

    This query is working fine but when we consider the case that we have

    more than 20,000 rows then how it will behave as we are checking row

    by row for each employee.

    Does anybody knows any other way to do this then please reply soon.

    Thanks,

    Rohit.

    Hi Rohit,

    The CASE statement is actually quite fast, you just need to adjust your code to only update the rows you need:

    UPDATE dbo.Table1

    SET Col1 =

    CASE EmployeeID

    WHEN 123 THEN 'abc1'

    WHEN 124 THEN 'abc2'

    WHEN 125 THEN 'abc3'

    END -- CASE EmployeeID

    WHERE EmployeeID IN (123,124,125);

    Note the additional WHERE clause. Make sure you have an index on EmployeeID (if appropriate, this depends on your current design of course.) If you are only updating the table once, a table scan will not kill you if only 20,000 rows are affected and the table is not too big.

    Best Regards,

    Chris Büttner

  • where exists

    would be more efficient than

    where in

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi Perry, could you please post an example?

    Best Regards,

    Chris Büttner

  • Actully your suggested solution is quite better but the problem is I have

    data in dataset and we cant transfer that data to temporary table,

    though we can insert it row by row but again insertion will take time.

    So, I am not able to use your solution.

  • Hi Rohit,

    I do not yet understand what you are trying to achieve.

    Could you please tell me more details about the process?

    Whats the relation between your UPDATE statement and

    the data set? And what are you trying to INSERT?

    Since you are working with a dataset, do you use a parameterized

    UpdateCommand? If not, this could be a performance killer.

    Best Regards,

    Chris Büttner

  • Hi,

    The problem exactly is like:

    I have some data in dataset (in .net), in that I have one table

    containing EmployeeId and Another Columns say Col1. This Col1

    consist the new data which I want to update in the DB.

    For This I have written the query which I have given in my very

    first post you can see first post.

    But that query is not that much efficient and I want to update

    the whole data in a single query with minimum time.

    I can use sqlserverbulkcopy solution also but this solution also has some

    demerits like later if we add some new column then it will fail.

    I hope you are clear with the problem now, can you please give me the

    best solution.

    Thanks,

    Rohit.

  • Rohit,

    So you have a table, let's call it Table1 with columns Employeeid and Col1. You are wanting to update the potentially 20,000 rows of Table1 at one time with new data for Col1. The big question that I have is how do you know what to put into Col1? Do you have a second table (this could be a text file, excel file, access database, SqlServer table, etc.), say Table2 with Employeeid (or something else we can match on that is also in Table1) and a column containing the data you want to put into Table1.Col1? If you do, then once you get it into a SqlServer table you can do the following.

    UPDATE t1

    Set Col1 = t2.DataForCol1 --Table2 has a column called DataForCol1

    --that contains the data for Column1

    FROM Table1 t1

    INNER JOIN Table2 t2 ON t1.Employeeid = t2.Employeeid

    Scott

  • Christian Buettner (9/8/2008)


    Hi Perry, could you please post an example?

    why?

    that's what i'm asking you 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi Scott D,

    I have the data comming from Excel file(Which is the latest one and

    want to update this data into the DB table) and am temporarily putting

    into one temp table.

    Thanks,

    Rohit.

  • Rohit (9/10/2008)


    Hi Scott D,

    I have the data comming from Excel file(Which is the latest one and

    want to update this data into the DB table) and am temporarily putting

    into one temp table.

    Thanks,

    Rohit.

    You have the data from excel file in SQL # table, you can simply use Scots query to update the data.

    UPDATE t1

    Set Col1 = t2.DataForCol1 --Table2 has a column called DataForCol1

    --that contains the data for Column1

    FROM Table1 t1

    INNER JOIN #Table2 t2 ON t1.Employeeid = t2.Employeeid

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply