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 |
|
emyk
Yak Posting Veteran
57 Posts |
 Posted - 2012-01-04 : 18:44:54
|
| I am attempting to update a zero value from column (c3) with a non zero value from c3. Here is a sample data on table1.cl c2 c3 c4 c5l 2 3 0 0.00 l 2 3 0 0.00l 2 3 0 0.00l 2 3 22 5.00 Here is a Desired table1:cl c2 c3 c4 c5l 2 3 22 0.00l 2 3 22 0.00l 2 3 22 0.00l 2 3 22 5.00 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 2012-01-04 : 19:51:21
|
| The business rule is that if C1 C2 AND C3 value are equal and if the value of C4 and C5 is zero then update C4 value with the maximum value of C4 where C4 is zero. Does this makes sense?something along this side.update #Table1set cl4 = (select max(a.cl4)from #table1 where a.cl1 = a.cl2 and a.cl3 = a.cl1and cl4 = '0' and cl5 = '0') |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-01-04 : 21:02:31
|
[code]update tset cl4 = m.cl4from #Table1 t inner join ( select cl1, cl2, cl3, cl4 = max(cl4) from #Table1 group by cl1, cl2, cl3 ) m on t.cl1 = m.cl1 and t.cl2 = m.cl2 and t.cl3 = m.cl3where t.cl1 = t.cl2and t.cl1 = t.cl3and t.cl4 = 0and t.cl5 = 0[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
emyk
Yak Posting Veteran
57 Posts |
Posted - 2012-01-05 : 09:48:06
|
| KH thanks for the response.I am getting syntax error unexpected '=' near "cl4 = max(cl4)"thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Update column value