| Author |
Topic |
|
xptm
Starting Member
7 Posts |
 Posted - 2004-09-01 : 11:42:17
|
Hello, i'm new in here... I have a situation where i have a statement defined like thisUPDATE Processo SET AmbitoEntidade_K = '$AmbitoEntidade_K$', NomeProcesso = '$NomeProcesso$', InicioPrevisto = '$InicioPrevisto$', FimPrevisto = '$FimPrevisto$' WHERE Processo_K='$Processo_K$'and at runtime i have to change the $fields$ by some values.The problem is that i can have or have not those values. When i don't have the values i want the field to be unchanged. But i have to fill in their space, and i don't know the current value it has.I wonder if there is some keyword like DEFAULT but that points to the current value of a field before the update.Or any other way, as a matter of fact... Thanks. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-01 : 12:25:03
|
well you could do this on your update:this will update all columns but NomeProcesso which will stay the same:declare @var1 varchar(50), @var2 varchar(50), @var3 varchar(50), @var4 varchar(50)select @var1 = '$AmbitoEntidade_K$', @var2 = null/*'$NomeProcesso$'*/, @var3 = '$InicioPrevisto$', @var4 = '$FimPrevisto$'UPDATE Processo SET AmbitoEntidade_K = (case when @var1 is null then AmbitoEntidade_K else @var1 end), NomeProcesso = (case when @var2 is null then NomeProcesso else @var2 end), InicioPrevisto = (case when @var3 is null then InicioPrevisto else @var3 end), FimPrevisto = (case when @var4 is null then FimPrevisto else @var4 end)WHERE Processo_K='$Processo_K$' Go with the flow & have fun! Else fight the flow :) |
 |
|
|
xptm
Starting Member
7 Posts |
Posted - 2004-09-01 : 13:00:03
|
| Well, not what i want but you put me in the right direction. While i'm substituting the $fiels$ i allready know if there is a value to substitute or not, so i just doUPDATE Processo SET AmbitoEntidade_K = 'K0001', NomeProcesso = NomeProcesso, InicioPrevisto = 'somevalue', FimPrevisto = 'somevalue' WHERE Processo_K='somevalue$'Many thanks for your help :) |
|
|
|
xptm
Starting Member
7 Posts |
Posted - 2004-09-01 : 13:01:09
|
| However i wonder if this is a costly operation in terms od processing... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-01 : 13:03:59
|
| the one you're using is not any costier that a normal update :) well it is a normal update :)a case is slower because it has to compare the values... but i'd say it's not so terrible.and how much data do you need to update?Go with the flow & have fun! Else fight the flow :) |
|
|
|
xptm
Starting Member
7 Posts |
Posted - 2004-09-01 : 13:32:14
|
| Up'til now the greater is this:UPDATE Processo SET AmbitoEntidade_K = 'K00037', NomeProcesso = 'Web Produção', InicioPrevisto = '', FimPrevisto = '', InicioEfectivo = InicioEfectivo, FimEfectivo = FimEfectivo, ProcessoTipo_K = ProcessoTipo_K, UtenteResponsavel_K = UtenteResponsavel_K, ProcessoEstado_K = ProcessoEstado_K, DescricaoProcesso = 'dsvfgasdfvasdfsadfsadf', PercentagemEfectivar = PercentagemEfectivar, HorasPrevisto = HorasPrevisto AtributoProcesso = AtributoProcesso, DataSistema = DataSistema WHERE Processo_K='K00005'I'm afraid that for each field=fieldname the database issue a 'SELECT fieldname FROM ...', and that will be probably costly... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-01 : 13:41:32
|
| there is only one table scan in the update to get the data to update. look at your execution plan.Go with the flow & have fun! Else fight the flow :) |
|
|
|
xptm
Starting Member
7 Posts |
Posted - 2004-09-01 : 13:49:02
|
| Well, i don't have access to the "server side" of the application, i'm just a client. In fact, the application is suposed to be database-independent, so it's probably up to the database the way it evaluates that.But i think it's satisfactory by now...Thanks again. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-01 : 13:51:17
|
| you don't have access to db and you're doing db access in you app???????and how do you test stuff and optimize it??? Query Analyzer is simply a must have for any db access developer...Go with the flow & have fun! Else fight the flow :) |
|
|
|
xptm
Starting Member
7 Posts |
Posted - 2004-09-01 : 14:36:43
|
| Well, yes i have access to test it, but this is to-be vendor-independent, so i look blindly to the database :) |
|
|
|
|
Old values on UPDATE