UPDATE multiple values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE multiple values

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-30 : 19:34:59

Hi,
I have a table which has 14000 records and another table with 10 records.

table name: EMP
fields: ID,Name,EmpId

table name: Sample
fields: sampleID, EmpId

The EmpId field in both tables have different set of values but sampleID of Sample table is the same as ID of Emp table.
Now, I want to update all the values of EMP.EmpdId to the values of Sample.EmpId for all EMP.ID = Sample.sampleID

Can anyone help me?
Thanks,
Mavericky

b

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-10-01 : 00:58:42

First thing, if you haven't done an update query before and you're using live/production data for business purposes, you'll want to be very careful. If you have 14k records to update, that's a lot of data to mess up.

It's probably a good idea to run a select statement first to make sure your joins are correct.

Step one:

Select Emp.Id, Emp.Name, Emp.EmpId, Sample.SampleId, Sample.EmpId
From Emp
Join Sample ON Sample.EmpId=Emp.Id

If your returned values look "right" then you can turn the select statement around into an update statement like this:

Update Emp
Set EmpId=Sample.EmpId
from Emp
Join Sample on Sample.Empid=Emp.Id

Again, test this first. If you can, maybe limit it to just 1 result so you don't do too much damage like this:

Update Emp
Set EmpId=Sample.EmpId
from Emp
Join Sample on Sample.Empid=Emp.Id
Where Emp.Id=1

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-01 : 01:02:10

Thanks a lot!!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-10-01 : 01:03:20

Welcome :) Hope it works out!

mavericky
Posting Yak Master

117 Posts

Posted - 2011-10-01 : 01:17:05

Actually, before seeing your reply I tried it in the same way and it worked. But I really appreciate you taking the time to analyze and respond :)

Sincerely,
Mavericky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources