February 16, 2010 at 2:47 pm
I have to update values to the Memkey and state fields in the TableA (Empkey,Memkey,State...) where Memkey is null, from 8 different databases. All those 8 databses has fields EmpKey and state.
I am dealing with a database of 1TB so looking for better performance when i update.
February 17, 2010 at 8:17 am
any reply... need to update multiple columns in a single update statement.
February 17, 2010 at 8:42 am
Not much of info to suggest.
You mentioned the database is 1 TB, what is the size of the table (TableA) and what are the underlying Indexes.
All I would suggest would be have your update statements added with suitable filtering conditions so that it would not update way too many records.
In short do it in small chunks
Keep an eye on the T-log file and its growth settings and the free disk space on the drive where it resides..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 17, 2010 at 9:15 am
Irrespective of the size of db and indexes, how would i run this update for multiple columns with above criteria in a single sql statement.
February 17, 2010 at 9:50 am
Again, there is not much to say as there is a vague information of what you are trying..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 17, 2010 at 10:35 am
query may be wrong for update, but my goal is to update 3 columns in table Emp_CA
select memkey into #t from dbo.Emp_CA where zen_memkey is null group by srckey
update zen_memkey.zen_memkey,zen_memkey.state,zen_memkey.empno
from #t as a inner join OBBEAVER.dbo.memberlist as b on
a.srckey=b.memkey group by b.empid,b.state
February 17, 2010 at 11:41 am
You don't assign any values in your update statement, that is why it's wrong..
Lookup BOL to see how an update statement is done, you have to assign values to each of your columns, separated by commas.
Cheers,
J-F
February 17, 2010 at 11:53 am
If the table is 1 TB in size, you will want to break the update into chunks or you could end up locking the entire table during the update.
Some idea of the table structures, indexes, the pertinent data in the tables, etc would greatly help us in helping you.
February 17, 2010 at 12:40 pm
There might be ways to loop through the data doing updates instead of large batches. Might be slower but less disruptive.
February 17, 2010 at 12:59 pm
Tara-1044200 (2/17/2010)
query may be wrong for update, but my goal is to update 3 columns in table Emp_CAselect memkey into #t from dbo.Emp_CA where zen_memkey is null group by srckey
update zen_memkey.zen_memkey,zen_memkey.state,zen_memkey.empno
from #t as a inner join OBBEAVER.dbo.memberlist as b on
a.srckey=b.memkey group by b.empid,b.state
Obviously the query is wrong..
There is no mention of the table Emp_CA in your update stmt. It is also missing "SET"
Whether or not your join condition is correct I can't say.
Either way you have to give more info what it looks like, what you would like to achieve..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 17, 2010 at 2:56 pm
I want to update memkey,state and empno in table Emp_CA where memekey is found with null value. these 3 columns should be updated from the table OBBEAVER.dbo.memberlist whic also has these 3 columns by joining them with memekey and srckey.
February 17, 2010 at 10:48 pm
Tara-1044200 (2/17/2010)
I want to update memkey,state and empno in table Emp_CA where memekey is found with null value. these 3 columns should be updated from the table OBBEAVER.dbo.memberlist whic also has these 3 columns by joining them with memekey and srckey.
You're not making it easy are you? 😀
Honestly, memkey or memekey? What is srckey? And so on.
The quality of the question doesn't really deserve an answer, but I am in an exceptionally benevolent frame of mind today, so:
UPDATE ECA
SET memkey = ML.memkey,
state = ML.state,
empno = ML.empno
FROM Production.Emp_CA ECA
JOIN OBBEAVER.MemberList ML
ON ML.srckey = ECA.srckey
WHERE ECA.memkey IS NULL;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 17, 2010 at 11:46 pm
Tara-1044200 (2/17/2010)
I want to update memkey,state and empno in table Emp_CA where memekey is found with null value. these 3 columns should be updated from the table OBBEAVER.dbo.memberlist whic also has these 3 columns by joining them with memekey and srckey.
I don't see how you can join on memkey if you specify memkey is null. Sure would be nice to have full table create scripts, sample data, expected outcomes...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 18, 2010 at 1:33 am
table names might be mis spelled wantedly but i just need logic. here is the structure of tables
table1 = OBBEVAER.MemberList(SrcKey,MemKey,state,empno)
table2 = Production.Emp_CA(SrcKey,MemKey,State,empno)
there are null values for MemKey in table1 but i can find values for that in table2 by joining SrcKey so my goal is to update MemKey on table1 from table2 and also corresponding values for state and empno from table2.
I Assume i explained it very clear.
February 18, 2010 at 3:42 am
Tara-1044200 (2/18/2010)
table names might be mis spelled wantedly but i just need logic. here is the structure of tables[snip]I Assume i explained it very clear.
Thanks. I have updated my previous post to reflect the new information.
I understand that you are just after the logic - but try to see the problem from our point of view.
Your intentions are good, but you will get better answers faster if you spend a few moments reading your own question from a stranger's point of view before hitting the submit button 🙂
@sqlguru: good catch concerning the NULL - I was coding in the dark there and missed that obvious problem 😀
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply