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 |
|
alexellice
Starting Member
13 Posts |
 Posted - 2012-11-29 : 04:30:39
|
| [code]update "THISTABLE"set col_C = 'S'from (select col_A, col_B, col_C , MAX(col_D) from "THISTABLE" group by col_A, col_B, col_C having MAX(col_D) < '19850101') as myDatawhere THISTABLE.col_A = '2607121497' and THISTABLE.col_B = mydata.col_B and THISTABLE.col_C = 'T'[/code]Hi all.. if anyone has some input on this it would be great.... my hair is all gray beacuse of this!.. Here we're using Pervasiv SQL (but normal SQL should work fine)All columns are strings and we have no Unique ID to work with :-(The problem is that when I run this... it says X rows affect... if I run a test query, say:select col_A, col_B, col_C from "THISTABLE" where col_C = 'T' and col_D < '19850101'It does not seem to have written to the rows |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 05:09:28
|
quote:
The problem is that when I run this... it says X rows affect... if I run a test query, say:select col_A, col_B, col_C from "THISTABLE" where col_C = 'T' and col_D < '19850101'It does not seem to have written to the rows
Post-update how can you see records with col_C = 'T' ? (Actually u r updating 'T' to 'S'.... right?)--Chandu |
 |
|
|
alexellice
Starting Member
13 Posts |
Posted - 2012-11-29 : 05:50:17
|
| I meant (select col_A, col_B, col_C, col_D from "THISTABLE" where col_C = 'T' and col_D < '19850101'sorry for the confusing.And yes... the whole idea of the query is to update all the col_C that have a value of 'T' to 'S' withing the select |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 05:53:38
|
| If you want to check the updated rows,select col_A, col_B, col_C from "THISTABLE" where col_C = 'S' and col_D < '19850101'--Chandu |
|
|
|
alexellice
Starting Member
13 Posts |
Posted - 2012-11-29 : 06:31:45
|
| I understand that... but by asking for 'T'... there should not be any rows with 'T'... only 'S' |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 06:38:32
|
quote:
Originally posted by alexellice
I understand that... but by asking for 'T'... there should not be any rows with 'T'... only 'S'
Yes.....--Chandu |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-29 : 07:09:36
|
| The updae will only affect rows where col_A = '2607121497'==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Update with conditional select.