[Info-ingres] correlated update with inline view?

[Roy Hann]

nikosv wrote:

> The following query works fine and does return not-null results :
>
> select * from   akana_eisa_epexa ak where   
>      c_grnoe = (
>      select c_grnoe from (select * from temp2,(  
>                          select am_asqenh, d_ekdoshs,  max(d_episkep) as
>      d_episkep
>                          from temp2     
>                          group by am_asqenh, d_ekdoshs) a
>                          where temp2.am_asqenh=a.am_asqenh and   
>                          temp2.d_ekdoshs=a.d_ekdoshs and
>                          temp2.d_episkep=a.d_episkep) b
>                                              where ak.am_asqenh=b.am_asqenh     
>                                              and ak.d_ekdoshs = b.d_ekdoshs
>                                              )
> but the following update dies with error 
> E_AD1012 An attempt to place a null value in a non-nullable datatype.
>     (Fri Jan 15 09:46:30 2016) 
>
> update akana_eisa_epexa ak set   
>      c_grnoe = (
>      select c_grnoe from (select * from temp2,(  
>                          select am_asqenh, d_ekdoshs,  max(d_episkep) as
>      d_episkep
>                          from temp2     
>                          group by am_asqenh, d_ekdoshs) a
>                          where temp2.am_asqenh=a.am_asqenh and   
>                          temp2.d_ekdoshs=a.d_ekdoshs and
>                          temp2.d_episkep=a.d_episkep) b
>                                              where ak.am_asqenh=b.am_asqenh     
>                                                 and ak.d_ekdoshs = b.d_ekdoshs
>                                              )
>
> or even tried with the FROM syntax:
>
> update   akana_eisa_epexa ak1
>    from akana_eisa_epexa ak  											 
>      c_grnoe = (
>      select c_grnoe from (select * from temp2,(  
>                          select am_asqenh, d_ekdoshs,  max(d_episkep) as
>      d_episkep
>                          from temp2     
>                          group by am_asqenh, d_ekdoshs) a
>                          where temp2.am_asqenh=a.am_asqenh and   
>                          temp2.d_ekdoshs=a.d_ekdoshs and
>                          temp2.d_episkep=a.d_episkep) b
>                                              where ak.am_asqenh=b.am_asqenh     
>                                                 and ak.d_ekdoshs = b.d_ekdoshs
>                                              )
> where ak1.am_asqenh=ak.am_asqenh
>
>
> still the same error
>
> is something like that even possible?

The syntax of the first update has been usable since Ingres 9.1. There
is a syntax error in your second update (missing SET) but once that is
fixed it is correct syntax for Ingres 10.0

The most obvious explanation is that the query really is returning null
for at least one c_grnoe. What if you CREATE TABLE...AS SELECT using the
subquery and then do HELP on it? Is c_grnoe nullable? And is there a row
where it is null?

Roy