I am trying to do a correlated update (rather rare condition) because
I have unique entries in table 'tylik' which correlate to multiple
entries in table 'mylik', in the sense of overwriting the 'c_typ_g'
value in table 'tylik'

update tylik t
set c_typ_g=
(select c_typ_g from mylik m
where t.c_typ_ylik=m.c_typ_ylik)

but I do get a :

E_US1401 Scalar subqueries not yet supported in this context.
(Tue Mar 15 11:49:07 2011)
End of Request - Terminated by Errors

this means that it will be supported in the future ?

I worked around it by making a procedure which does the overwriting
going row by row but it would be cool if this feature would be
implemented

Try this

update tylik t
FROM mylik m
set c_typ_g=m.c_typ_g
where t.c_typ_ylik=m.c_typ_ylik

On 15 March 2011 09:55, nikosv <nikosv01 (AT) gmail (DOT) com> wrote:



--
Peter Gale
pgale61 (AT) gmail (DOT) com

On Mar 15, 12:56*pm, Peter Gale <pgal... (AT) gmail (DOT) com> wrote:

hi
gives :

E_US125D Ambiguous replace: you have specified several replacements
for
the same row.
(Tue Mar 15 13:05:15 2011)

which is what I am trying to avoid

On Mar 15, 2011, at 7:06 AM, nikosv wrote:


Are the m.c_typ_g's in fact ambiguous? or is there only one
m.c_typ_g value per m.c_typ_ylik, but multiple occurrences?

If the latter, you could try:
declare global temporary table session.upd as
select distinct m.c_typ_g, m.c_typ_ylik
from mylik m
where m.c_typ_ylik in (select c_typ_ylik from tylik)
on commit preserve rows with norecovery;

update tylik t
from session.upd u
set c_typ_g = u.c_typ_g
where t.c_typ_ylik = u.c_typ_ylik;

drop session.upd;

the where-clause on the declare global temporary table is
optional, if most of the mylik table is selected it wouldn't be
worth it.

Karl

The error message would indicate that you are running an older version
of Ingres.
9.3 onwards supports the scalar subqueries though you would probably
still need to handle the likely problem of non-uniqueness of mylik -
this is why the ambiguous replace messages appear as tylik records would
each be potentially updated multiple times. You need to decide which
mylik records value will be used to update a given tylik. If it is
arbitrary, you could use min or max to single out one value - or if the
correlated m.c_typ_g all happen to be the same then making the
sub-select distinct may help.
Regards,
Ian

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
nikosv
Sent: 15 March 2011 09:55
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] correlated update?

I am trying to do a correlated update (rather rare condition) because
I have unique entries in table 'tylik' which correlate to multiple
entries in table 'mylik', in the sense of overwriting the 'c_typ_g'
value in table 'tylik'

update tylik t
set c_typ_g=
(select c_typ_g from mylik m
where t.c_typ_ylik=m.c_typ_ylik)

but I do get a :

E_US1401 Scalar subqueries not yet supported in this context.
(Tue Mar 15 11:49:07 2011)
End of Request - Terminated by Errors

this means that it will be supported in the future ?

I worked around it by making a procedure which does the overwriting
going row by row but it would be cool if this feature would be
implemented
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

yes,for example :


c_ylikoy ¦c_typ_ylikl¦c_typ_g
+----------+--------
+-----------------------------------------------------------
¦1200003503¦12000035¦38727
¦1200003504¦12000035¦13380
¦1200003502¦12000035¦13380
¦1200003505¦12000035¦13380
L----------+--------
+-----------------------------------------------------------

the requirements are relaxed so any value will work.I do not care what
value gets there in the end hence I don't mind it being overwriten

thanks

On Mar 15, 2011, at 8:21 AM, nikosv wrote:

So what you are trying to do is ill-defined anyway.

You can still use the temp table intermediate, but
you'll have to select some c_typ_g (e.g. min or max)
for each c_typ_ylik.

Karl

that is very true but it is done deliberately

thanks for the hints
from the discusion, I also found out that the correlated update works
on greater versions; very usefull to know