Re: Urgent - please help with update sql

What about : UPDATE gl_movement SET (gllm_movement) = ((SELECT actuals FROM tmp_jjc3 WHERE budget_magic = gl_movement.gllm_magic AND period = gl_movement.gllm_period)) WHERE EXISTS (SELECT gllm_period FROM tmp_jjc3 WHERE budget_magic = gl_movement.gllm_magic AND period = gl_movement.gllm_period) Otherwise, try reducing the number of rows in tmp_jjc3 - and do your update 2 or 3 times - but 7000 rows is not that much. Did you update stats ? Even on the temp table ? >>> Jenny <jjc@eec.co.nz> 03/08/00 10:23am >>> --------------A2841D263D32D4478BC77A3D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi Hoping someone can give me some help. I've written an update sql for informix which should be updating about 7000 records - it works fine when I test it on only a few records, but it doesn't ever end when I run it against all the data. when I eventually cancel out of it I get "ISAM error: no record found - Error -111" I use five rather large temp tables to get my update data, which I've tried dropping before the update. I've also locked the table in exclusive mode, and put indexes on two of the fields (there's only three fields in each row), but still no joy. table tmp_jjc3 contains the fields: acct, period, actuals, budget_magic; table gl_movement contains the fields gllm_magic, gllm_period, gllm_movement, has indexes on gllm_magic (duplicates allowed) and gllm_magic/gllm_period (unique) The last part of my sql is below. I'd be really grateful if someone could tell me where I'm going wrong. Thanks! create index ix_magic on tmp_jjc3(budget_magic,period); begin work; lock table gl_movement in exclusive mode; update gl_movement set gllm_movement = (select actuals from tmp_jjc3 where budget_magic = gllm_magic and gllm_period = period) where gllm_magic in (select budget_magic from tmp_jjc3) and gllm_period in (select period from tmp_jjc3) Jenny

Richard harnden wrote: > What about : > > UPDATE gl_movement > SET (gllm_movement) > = ((SELECT actuals > FROM tmp_jjc3 > WHERE budget_magic = gl_movement.gllm_magic > AND period = gl_movement.gllm_period)) > WHERE EXISTS (SELECT gllm_period > FROM tmp_jjc3 > WHERE budget_magic = gl_movement.gllm_magic > AND period = gl_movement.gllm_period) > > ... > > begin work; > lock table gl_movement in exclusive mode; > update gl_movement > set gllm_movement = > (select actuals > from tmp_jjc3 > where budget_magic = gllm_magic > and gllm_period = period) > where gllm_magic in (select budget_magic from tmp_jjc3) > and gllm_period in (select period from tmp_jjc3) > In fact, the problem is a little more insidious than just performance. Not only will Richard's SQL work faster, its also correct. The SQL that uses where gllm_magic in (select budget_magic from tmp_jjc3) and gllm_period in (select period from tmp_jjc3) could update some rows to null because the "outer" WHERE clause does not match the inner one that gets the value to be updated. For example, consider the following : Assume that gl_movement has gllm_magic gllm_period 1 1 2 2 3 2 while tmp_jjc3 has the following budget_magic period 1 1 2 2 3 1 gllm_movement in the 3:2 row will get set to null. Rudy