Sybase Database - Update or No update in a single sql

Author

Update or No update in a single sql

Deepak

2006-03-31, 7:23 am

Hi ,
I have scenario where i need to update a column only when its value is
equal to 3.
But i want to use a single query.
Here is the query
create table #temp ( col int )

update #temp
set col=
case
when col = 3 then 1
else col
end

The disadv. of above query is that even when col is 3, it is been
overwritten by value 3.

Can anybody give a better performance solution. or modify the above
query,

Thanks

B. Tkatch

2006-03-31, 11:23 am

What's wrong with a standard WHERE clause?

UPDATE #temp SET col = 1 WHERE col = 3.

B.

Deepak

2006-04-01, 3:23 am

Actually there could be more than one columns in table
where the 'where clause won't work'

for eg set col1=
case
when col1 = 3 then 1
else col1
end ,
col2=
case
when col2 = 3 then 1
else col2
end

Any pointers

B. Tkatch

2006-04-02, 3:23 am

Being the criterion for when a COLUMN is to be UPDATEd is different for
each COLUMN, it would seem that you want two different queries with two
different clauses.

If the COLUMNs are not indexed and a full table scan is performed, the
CASE will definitely be faster because it is only one tablescan,
however this will actually UPDATE each and every COLUMN each and every
time.

B.

Deepak

2006-04-02, 3:23 am

Thanks B.Tkatch ,
So which one would you recommend.

Scanning twice or update each col every time.
Which one is more economical , update/select

B. Tkatch

2006-04-02, 1:23 pm

I am not an expert with Sybase in the slightest. I have very limited
use of it right now.

I think it matters on how many rows are hit. If more than half are hit,
a full table scan is better anyway.

Most likely, however, the separate queries are better. It may also be a
good idea to index the columns first. If the creation of the index
itself doesn't take too long, it may be worth it to CREATE it just for
this query.

B.

Carl Kayser

2006-04-03, 7:23 am

"Deepak" <deepak.rathore@gmail.com> wrote in message
news:1143965442.471735.272370@t31g2000cwb.googlegroups.com...
> Thanks B.Tkatch ,
> So which one would you recommend.
>
> Scanning twice or update each col every time.
> Which one is more economical , update/select
>

"It depends." How many rows are on the table? How wide is the table? How
many rows will be changed for col1? For col2? Are there any indexes which
use col1 or col2? How many indexes? Are any useful for the updates?

sundar

2006-04-07, 1:23 pm

ALso update triggers.
For e.g if you use 2 or more update statements the trigger will be fired for
each update stmts

btw,if case is not working then
will the following work

update #t1 set col1=isnull(nullif(c
ol1,3),3),col2=isnul
l(nullif(col2,4),4)
etc...

As I am novoice in databases please check the performance

Thanks,
V.Sundar

Carl Kayser wrote:
>
>"It depends." How many rows are on the table? How wide is the table? How
>many rows will be changed for col1? For col2? Are there any indexes which
>use col1 or col2? How many indexes? Are any useful for the updates?

Sponsored Links