[Info-Ingres] Ambiguous replace?

[iinubee at yahoo.com]

On Apr 6, 6:17 am, "Tim Ellis" <tell... at csc.com> wrote:
> If I run the following bit of SQL
>     Select * from View_A a, View_B b
>     where a.Column_2 = b.Column_2
>          and a.column_3 = b.column_3
>          and a.column_2 like 'Q%'
>          and b.column_2 like 'Q%'
>
> then I get back two rows - They have the same value for column_1 but
> different values for
> column_3 and  View_B.Column_1
> However if I run
>
>     Update VIew_A a from view B b
>     Set Column_1 = b.Column_1
>     where a.Column_2 = b.Column_2
>          and a.column_3 = b.column_3
>          and a.column_2 like 'Q%'
>          and b.column_2 like 'Q%'
>
> I get:-
> E_US125D Ambiguous replace: you have specified several
> replacements for the same row.
>
> Surely if I had several replacements for the same row I would see this
> in the original select.


This is just straightforward SQL.

According to your statements, the original select gives you two rows
with same View_A.Column_1, and differing Column_3 and
View_B.Column_1.  So, when you go ahead and try to do an Update table
A, Ingres finds that there are two matching rows and does not know
which one to use as the new value to update in View_A.

Solution is to make sure your select gives you only one row for each
distinct value in View_A.Column_1.