[Info-Ingres] Ambiguous replace?

[Tim Ellis]

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.