September 8, 2008 at 4:32 am
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.
September 8, 2008 at 4:48 am
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]
September 8, 2008 at 9:14 am
Your example makes it look like the Col1 value might be calculated as a function of EmployeeId. Is this true?
September 8, 2008 at 9:40 am
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
September 8, 2008 at 1:37 pm
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]
September 8, 2008 at 2:38 pm
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
September 8, 2008 at 3:46 pm
where exists
would be more efficient than
where in
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 8, 2008 at 4:14 pm
Hi Perry, could you please post an example?
Best Regards,
Chris Büttner
September 8, 2008 at 11:24 pm
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.
September 9, 2008 at 12:38 pm
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
September 9, 2008 at 11:24 pm
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.
September 10, 2008 at 6:49 am
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
September 10, 2008 at 9:43 am
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" 😉
September 10, 2008 at 10:42 pm
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.
September 11, 2008 at 12:58 am
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