从两个表中获取一个表中的更新的SQL查询错误 [英] Get error in SQL query for update in one table from two tables

问题描述

使用ingres数据库从两个表记录中更新一个表





E_US0B70第1行，在此上下文中不允许聚合函数'max'。



table_one，table_two和table_three
$ b table_one中$ b

我必须更新两个日期列作为查询aprl_to_dt和aprl_fr_dt



其中aprl_to_dt来自table_two作为列，aprl_fr_dt来自table_three

因为记录是重复的所以需要最大日期应该在table_one中更新



我尝试过：



i已经尝试了

update one table from two tables records

as im using ingres data base.
E_US0B70 line 1, aggregate function 'max' not allowed in this context.

table_one, table_two and table_three

in table_one i have to update two date columns as query aprl_to_dt and aprl_fr_dt

where aprl_to_dt from table_two as column and aprl_fr_dt from table_three
as records are duplicate so in need max of date should be update in table_one

What I have tried:

i have tried

update table_one a
from table_two b , table_three c
set a.aprl_to_dt= b.aprl_fr_dt1,
a.aprl_fr_dt =  max(c.aprl_entdt) 
where b.aprl_cmpcd='01' and b.aprl_flag='CNF' and b.aprl_status='ECNF'
 and (b.aprl_fr_dt1 is not null or b.aprl_fr_dt1 !='')
 and b.aprl_cmpcd =a.aprl_cmpcd
and b.aprl_flag=a.aprl_flag and b.aprl_status = a.aprl_status
and b.aprl_empcd= a.aprl_empcd and b.aprl_due_dt = a.aprl_due_dt 
and b.aprl_empcd = c.aprl_empcd and b.aprl_cmpcd = c.aprl_cmpcd and b.aprl_due_dt = c.aprl_incdue
group by  b.aprl_empcd, b.aprl_fr_dt1, b.aprl_to_dt1;

i已经尝试了

i have aslo tried

update table_one a
from table_two b
set a.aprl_to_dt= b.aprl_fr_dt1,
a.aprl_fr_dt in (select max(c.aprl_entdt) from table_three c where
 b.aprl_empcd = c.aprl_empcd and b.aprl_cmpcd = c.aprl_cmpcd and b.aprl_due_dt = c.aprl_incdue
group by  b.aprl_empcd, b.aprl_fr_dt1, b.aprl_to_dt1)
where b.aprl_cmpcd='01' and b.aprl_flag='CPL' and b.aprl_status='ECPL'
 and (b.aprl_fr_dt1 is not null or b.aprl_fr_dt1 !='')
 and b.aprl_cmpcd =a.aprl_cmpcd
and b.aprl_flag=a.aprl_flag and b.aprl_status = a.aprl_status
and b.aprl_empcd= a.aprl_empcd and b.aprl_due_dt = a.aprl_due_dt

但

but

_US09C8 line 1, Syntax error on 'in'.  The correct syntax is:
    UPDATE table [alias]
       [FROM table {, ... }]
       SET column = expression|NULL {, ... }
       [WHERE search_condition]
     or:
     UPDATE table [alias]
       [FROM table {, ... }]
       SET (column,column,...) = (SELECT expr,expr,... FROM ... )
       [WHERE search_condition]

推荐答案

此处没有GROUP BY选项更新语法（如图所示）。



你之前完成任何总结;然后在UPDATE中使用结果集。

There is no GROUP BY option in the UPDATE "syntax" (as indicated).

You do any summing BEFORE; then use the "result set" in the UPDATE.

这篇关于从两个表中获取一个表中的更新的SQL查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！