sybase for loop in sql

Question

Hi,

I need to update a column value in a table based on values from a Other 2 tables.

I was able to write a update statement to achieve this:

 update T3 set col=T1.x from T1,T2,T3 where ...

This takes lot of time since the table T3 has more than million records.

I would like to get the value v1 from T1 and loop over them and get corresponding value for v1 from t2 as v2 and use v2 to update all records where colx=v1;.

How do i use loop to perform select , get value and then do update in sql?

I use sybase db.

Thanks.

Answer 1

How do i use loop to perform select , get value and then do update in sql?

You don't (as Unreason has commented) - not unless you are convinced that you can write a better database engine than Sybase have managed in over twenty years.

Why not post the full update statement, so that StackOverflow users can offer suggestions on improving its performance?

Answer 2

I agree with Unreason and Mark. With the actual UPDATE statement, we can help you; with your description, we can't. I need the CREATE TABLE statements for the three tables as well.

There are other questions as well. Why are you referencing T2 (all your data is sourced from T1) ?

A million rows is nothing for Sybase: 1 can read 16 million rows in 13.2 seconds on my little demo server. The fact that you refer to them as 'records' is evidence of your one-record-at-a-time mindset. Sybase is a set-processing engine for Relational (set oriented) databases. If we see the DDL, we can identify why it is slow. That way, we will be correcting the problem at the source, rather than compounding it by adding a serial batch process to it.

If, on the other hand, you are stuck with record processing, then at least write code to execute that on a massively parallel server.