Question

我试图用来自另一个表格的数据更新一个表格列 deletiondate , 但我得到了“ 缺失表达式” 错误。有人能帮助我解决这个问题吗?

基本上我要在表格中更新 < code> delectate 列, 将关键字段与另一表格合并, 并分组。如果日期为 < code> 01- JAN-0001 , 记录计数为 & gt; 1, 那么需要更新 < code> 01- JAN-0001 , 则我需要更新最大删除日期值。

更新报表一使用了:

update table1 db1 set deletiondate =

SELECT  
CASE WHEN count(*)>1 and ( 
    (select 1 
     from table2 b 
     where b.loginid = a.loginid
         and a.creationdate = b.creationdate 
         and b.deletiondate =  01-JAN-0001 
     ) = 1) THEN  01-JAN-0001  ELSE to_char(MAX(deletiondate), DD-MON-YYYY ) END as deletiondate1  
FROM table2 a 
GROUP BY a.loginid, a.creationdate 
WHERE db1.userloginid = a.loginid and db1.usercreationdate = a.creationdate

Answer 1

使用此格式 : < a href=" "http://www.sqlfiddle.com/#.4/c46a6/2" rel="nofollow" >http://www.sqlfiddle.com/#.4/c46a6/2

update product set
  (total_qty,max_qty) = 
(
  select sum(qty) as tot, max(qty) as maxq
  from inv
  where product_id = product.product_id
  group by product_id
) ;

抽样数据:

create table product(
  product_id int primary key,
  product_name varchar(100),
  total_qty int,
  max_qty int
);

create table inv(
  inv_id int primary key,
  product_id int references product(product_id),
  qty int
);


insert into product(product_id,product_name) 
select 1, KB  from dual
union
select 2,  MSE  from dual
union
select 3,  CPU  from dual;

insert into inv(inv_id,product_id,qty)
select 1,1,4 from dual
union
select 2,2,1 from dual
union
select 3,3, 3 from dual
union
select 4,1,1 from dual
union
select 5,2,2 from dual
union
select 6,1,5 from dual;

查询输出 :

| PRODUCT_ID | PRODUCT_NAME | TOTAL_QTY | MAX_QTY |
|------------|--------------|-----------|---------|
|          1 |           KB |        10 |       5 |
|          2 |          MSE |         3 |       2 |
|          3 |          CPU |         3 |       3 |

友情链接