[Info-Ingres] Ambiguous replace?

[simonl at unwired.com.au]

On Apr 11, 7:30 pm, "Tim Ellis" <tell... at csc.com> wrote:
> On Apr 6, 5:33 pm, iinu... at yahoo.com wrote:
>
>
>
> > 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.
>
> Either I didn't clearly explain, or you didn't follow - The values of
> Column_3 are distinct
>
> Table_A
> Column_1   Column_2   Column_3
> "   "             "Q1234"     13590
> "   "             "Q1234"     66000
>
> Table_B
> Column_1   Column_2   Column_3
> "69804"      "Q1234"     13590
> "86592"      "Q1234"     66000
>
>     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%'
>
> gives 2 rows
>
>     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%'
>
> E_US125D Ambiguous replace: you have specified several replacements
> for  the same row.- Hide quoted text -
>
> - Show quoted text -

You can find the errant rows with:
select a.column_2, a.column_3, count(*)
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%'
group by a.column_2, a.column_3
having count(*) > 1

If it is truly working as you describe then that would be a bug!  Log
a support issue.  They will want a test case (i.e. copydb output &
data) to support your accusation.  Or you could try and work around it
by removing the views from the SQL.