Hi All,

I'm trying to do the following update...
update holding_3 h
from tiny_int_speed t
set
field3 = case when field3 is null and t.field_id = 3 then t.value end,
field4 = case when field4 is null and t.field_id = 4 then t.value end
where h.maid = t.maid and t.maid > 0

Trouble is I keep getting ambiguous replaces.

This is due to all the cases in the t table where t.field_id is not 3 or not 4.

Anyone got any ideas?

Here are some table descriptions.
help table holding_3\g
Executing . . .

Name: holding_3
Owner: ace
Created: 03/02/2011 15:46:43
Location: ii_database_6
Type: user table
Version: II9.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 14
Row width: 14
Number of rows: 2
Storage structure: isam with unique keys
Compression: none
Duplicate Rows: not allowed
Number of pages: 4
Overflow data pages: 0
Journaling: disabled
Base table for view: no
Permissions: none
Integrities: none
Optimizer statistics: none

Column Information:
Key
Column Name Type Length Nulls Defaults Seq
maid integer 4 no no 1
field3 integer 4 yes null
field4 integer 4 yes null


‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ƒ
�maid �field3 �field4 �
†€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€‡
� 1� � �
� 2� � �
„€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€…
(2 rows)

help table tiny_int_speed\g
Executing . . .

Name: tiny_int_speed
Owner: ace
Created: 03/02/2011 15:46:56
Location: ii_database_6
Type: user table
Version: II9.0
Page size: 2048
Cache priority: 0
Alter table version: 0
Alter table totwidth: 17
Row width: 17
Number of rows: 8
Storage structure: isam
Compression: none
Duplicate Rows: allowed
Number of pages: 4
Overflow data pages: 0
Journaling: disabled
Base table for view: no
Permissions: none
Integrities: none
Optimizer statistics: none

Column Information:
Key
Column Name Type Length Nulls Defaults Seq
maid integer 4 no no 1
field_id integer 4 no no
array_index integer 4 no no
value integer 4 yes yes

Secondary indexes:
Index Name Structure Keyed On
tiny_int_speed_field_id btree field_id, array_index

‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€€€€€€€ €€€€€€ƒ
�maid �field_id �array_index �value �
†€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€€€€€€€Š€€€€€€€ €€€€€€‡
� 1� 1� -1� 3�
� 1� 2� -1� 3�
� 1� 3� -1� 4�
� 1� 4� -1� 5�
� 2� 1� -1� 6�
� 2� 2� -1� 7�
� 2� 3� -1� 8�
� 2� 4� -1� 9�
„€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€€€€€€€‰€€€€€€€ €€€€€€…

Martin Bowes

On Feb 3, 2011, at 11:02 AM, Martin Bowes wrote:

Would it work if you added the case conditions to the where clause?
where ... and ((h.field3 is null and t.field_id = 3) or (...field4...))

Karl

Hi Karl,

I'll give it a try...

Just to give you an idea of the scale of this one....
Imagine the holding table has about 370 columns making it wide enough to require a 64k page for each row. There are 500,000 rows.

Depending on the column datatype we run the update against one of 4 tables (int_speed, real_speed...). The int_speed table has over 36milllion rows.

The program I'm trying to improve on does one column at a time. Which is easy to program, but each update requires each page to be logged. So all 370 columns can take about two days and whoa betides anything else that tries to start!

BTW. I converted the tables into Vectorwise equivalents and ran the original program...in 30 minutes.

Does VW F'ing ROCK or what!

Sadly the boss wants me to try about a million combinations of techniques to try and make standard Ingres run to an acceptable time scale.

I'll let you know how that works out.

Marty
________________________________________
From: Karl Schendel [schendel (AT) kbcomputer (DOT) com]
Sent: 03 February 2011 16:33
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] conditional update

On Feb 3, 2011, at 11:02 AM, Martin Bowes wrote:

Would it work if you added the case conditions to the where clause?
where ... and ((h.field3 is null and t.field_id = 3) or (...field4...))

Karl



_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres