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 |
|
Exir
Posting Yak Master
151 Posts |
 Posted - 2009-06-05 : 06:29:47
|
| HiThere is two column in my table(program and status), I want to check all rows for a program(forexample program number 1) and if all status fields for that program was equal 'CURRENT' , then fill another field with current. and if all status fields for that program was equal 'DONE' , then fill another field with done and if some fields were equal current and some fields were done, then fill the third field with 'BOTH'How can i do it? please help |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 06:34:35
|
[code]update tset another_field = case when s.rows_cnt = curr_cnt then 'CURRENT' when s.rows_cnt = done_cnt then 'DONE' else 'BOTH' endfrom mytable t inner join ( select program, rows_cnt = count(*), curr_cnt = sum(case when status = 'CURRENT' then 1 else 0 end), done_cnt = sum(case when status = 'DONE' then 1 else 0 end) from mytable group by program ) s on t.program = s.program[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2009-06-05 : 06:48:33
|
| Sorry it is abit complicated:( could you explain more?How it read the table row by row to check the statuses for program? what is inner join used for? What is S ? Is it another name for mytable? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 06:52:52
|
Why do need to read row by row when you can perform the operations using set based.S is the name given to the derived table. T is the alias for mytable. The derived table will gives per program, total no of rows, how many rows are CURRENT, how many are DONE. And these information is then used to determine what to assign the another_field
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2009-06-05 : 07:19:22
|
| I got it, thank you very much.but what if another_field be in other table ? we cant join two tables which dont have same number of fields, can we? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 07:25:42
|
yes. you can
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2009-06-05 : 08:55:02
|
| This froum is the best forum i have ever seen,our problems always have quick and correct responses.My knowledge about SQL is rising everyday with the help of this forum.thank you so much again |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-05 : 08:56:21
|
you are welcome
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
reading table row by row