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.
| Author |
Topic |
|
raaj
Posting Yak Master
129 Posts |
 Posted - 2009-05-17 : 06:18:00
|
| Hi Guys,I am having a problem with Update statement......Here is the sample data and my question : Create table temp1 (DeptId varchar(4), deptname varchar(20),location varchar(20))Insert into temp1 values ('1','Sales','dallas')Insert into temp1 values ('3','marketing','new jersey')Insert into temp1 values ('4','shipping','newyork')Insert into temp1 values ('5','production','dallas')Insert into temp1 values ('6','hiring','california')Insert into temp1 values ('8','recruiting','virginia')Insert into temp1 values ('9','consultancy','Houston')--Select * from temp1Create table temp2(dept varchar(25), equivalentId varchar(10))Insert into temp2 values ('Sales','10')Insert into temp2 values ('marketing','12')Insert into temp2 values ('shipping','15')Insert into temp2 values ('production','43')Insert into temp2 values ('hiring','29')--Select * from temp2Now I want to write update statement such that the 'equivalentid' field values from temp2 gets updated into deptname field of temp1 table and the values which are not matching should be replaced by empty spaces i.e.('')The final result set after updating when I run this statement (select * from temp1) should be like this :DeptId deptname location1 10 dallas3 12 newjersey 4 15 newyork5 43 dallas6 29 california8 virginia9 Houston |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-17 : 06:57:08
|
update t1set t1.deptname = t2.equivalentId from table1 as t1left join table2 as t2 on t2.dept = t1.deptname
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
raaj
Posting Yak Master
129 Posts |
Posted - 2009-05-17 : 23:48:10
|
| Thanks Peso...It worked perfectly........ |
|
|
|
|
|
|
|
|
Update question...........