高分求助：update语句；产品制定了统一零售价后，分销价格的销售价，最高/最低价必须和统一零售价一致，并更改状态。。

Question

求助有关update的sql或存储过程
还是分销行业的实际应用
先介绍相关表和字段。
表有“产品表”，“价格表”
产品表定义了是否有“统一零售价”字段。“统一零售价”；价格表中定义了“最高上限价”，“最低下限价”，“终端销售价”，“状态”，“pos机发布”，“有效日期”，“更新日期”。
其中价格表有产品表的外键产品表：product_info
piid         ----     union_flag          ----        union_price
产品编号           是否统一零售价(1是）         统一零售价
-------------
价格表：price
pid -- ppid -- client_price -- max_price --  min_price -- status -- pos_status -- valide_date --  update_date
价格Id，产品id，终端销售价，   最高上限价，  最低下限价， 状态，    pos状态，    有效日期，     更新日期----------------------------------
现在数据库中有部分数据价格表中的最高价或最低价或终端销售价和产品表中指定的统一零售价不同
这时要把这些数据修改，修改成和相关的产品表中的统一零售价一致，并同时更改status=1,pos_status=1, valide_date,update_date为当前日期
请问怎么修改呢？价格表中只考虑status 为1，2的状态

Answer 1

查询select的语句。。
--制定了统一零售价的商品价格数据
select p.sc_tt_price_id, p.sc_tm_product_info_id, p.sc_tm_dealer_id,
       pi.uni_sale_price, p.client_retail_price, p.max_price, p.min_price,
       p.valid_date, p.update_date,
       p.status, p.pos_delivery_status
--select count(p.rowid) 
 from sc_tt_price p, sc_tm_product_info pi
 where 1=1
 and p.sc_tm_product_info_id = pi.sc_tm_product_info_id
 and pi.uni_sale_price_flg = '1'
 and p.status in (1, 2)

Answer 2

update sc_tt_price p set (p.client_retail_price, p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 );
                     
commit;

Answer 3

不知道怎么搞的，刚才还有200多条数据的。只有一条产品表的统一零售价和价格表中的销售价不同。。我运行上面的SQL后，，200多条数据只有一条了。。（非正式生产数据库）
还有我运行下面这段sql,只是增加了pp.max_price, pp.min_price...数据库报空。不知道为什么。。update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, pi.uni_sale_price, pi.uni_sale_price, '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 );
                     
commit;加了黑体这个后，报错：无法更新,max_price为null....
求助
sql哪里有问题么。

Answer 4

更新的原则是price表中的最高价或最低价或终端销售价和相关的产品表中制定的统一零售价不同。
要把价格表中的这些价格都更新成和产品表中的终端销售价相同

Answer 5

报错：无法更新,max_price为null....原因是不是你建表时候对max_price有not null 约束？

Answer 6

表不是我建的。我看了下文档，max_price, min_price是有not null 约束。。
那怎么解决呢？这些数据都不是空的啊。。

Answer 7

由于你的是非正式生产数据库，也就是相当于测试区，多客户端的操作会导致你这样
其它人给你delete 掉了，并commit   于是你select出来的就和之前的不一样

Answer 8

非空约束，当然不能insert 到该栏位，用NVL处理下嘛
比如当max_price为空时候，将它设置为0: nvl(max_price,0)

Answer 9

select pi.uni_sale_price,pi.uni_sale_price, pi.uni_sale_price, '1', '1', sysdate, sysdate
from sc_tt_price pp, sc_tm_product_info pi
where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
and pi.uni_sale_price_flg = '1'
and pp.status in (1, 2)
and  pp.sc_tt_price_id= --换成你sc_tt_price.sc_tt_price_id ，带入具体数据进去，
                           --你查看下你对应的sc_tm_product_info表中 uni_sale_price是不是空的？

Answer 10

---你先查看下你select出来的是不是原本就是null
update sc_tt_price p set (p.client_retail_price, p.max_price,, --哥们，你这里多了一个逗号？？？
p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 );
                     
commit;

Answer 11

没多逗号那是因为我原来在set里面写nvl(max_price,0),在论坛里去掉这个后，忘了去掉逗号
我不知道怎么回事啊。。
数据不可能是null，有非空约束的

Answer 12

你按照我10楼的代码，带入具体值进去查询出来sc_tm_product_info表中 uni_sale_price不为空？

Answer 13

建表 + 测试数据create table PRICE
(
  PID          NUMBER(2),
  PPID         NUMBER(2),
  CLIENT_PRICE NUMBER(10),
  MAX_PRICE    NUMBER(10),
  MIN_PRICE    NUMBER(10),
  STATUS       VARCHAR2(2),
  POS_STATUS   NUMBER(2),
  VALIDE_DATE  DATE,
  UPDATE_DATE  DATE
)
;prompt Creating PRODUCT_INFO...
create table PRODUCT_INFO
(
  PIID        NUMBER(2),
  UNION_FLAG  VARCHAR2(1),
  UNION_PRICE NUMBER(10)
)
;prompt Loading PRICE...
insert into PRICE (PID, PPID, CLIENT_PRICE, MAX_PRICE, MIN_PRICE, STATUS, POS_STATUS, VALIDE_DATE, UPDATE_DATE)
values (1, 1, 90, 91, 89, '2', 2, to_date('03-01-2011', 'dd-mm-yyyy'), to_date('03-01-2011', 'dd-mm-yyyy'));
insert into PRICE (PID, PPID, CLIENT_PRICE, MAX_PRICE, MIN_PRICE, STATUS, POS_STATUS, VALIDE_DATE, UPDATE_DATE)
values (2, 2, 90, 91, 89, '2', 2, to_date('03-01-2011', 'dd-mm-yyyy'), to_date('03-01-2011', 'dd-mm-yyyy'));
insert into PRICE (PID, PPID, CLIENT_PRICE, MAX_PRICE, MIN_PRICE, STATUS, POS_STATUS, VALIDE_DATE, UPDATE_DATE)
values (3, 3, 300, 300, 300, '1', 2, to_date('03-01-2011', 'dd-mm-yyyy'), to_date('03-01-2011', 'dd-mm-yyyy'));
commit;
prompt 3 records loaded
prompt Loading PRODUCT_INFO...
insert into PRODUCT_INFO (PIID, UNION_FLAG, UNION_PRICE)
values (1, '1', 100);
insert into PRODUCT_INFO (PIID, UNION_FLAG, UNION_PRICE)
values (2, '0', 200);
insert into PRODUCT_INFO (PIID, UNION_FLAG, UNION_PRICE)
values (3, '1', 300);
commit;
更新SQL-- 更新 SQL
  -- 更新结果 更新一条记录 ppid = 1    
  update price a set (client_price, max_price, min_price, status, pos_status, valide_date, update_date) =
         (select b.union_price,b.union_price,b.union_price,'1','1',sysdate,sysdate
              from product_info b
                  where a.ppid = b.piid 
                        --and a.status in ('1','2')
                        --and b.union_price != (a.client_price + a.max_price + a.min_price) / 3
                        )
                        where exists
                        (select 1 from product_info c 
                                where a.ppid = c.piid 
                                      and a.status in ('1','2')
                                      and c.union_flag = '1'
                                      and c.union_price != (a.client_price + a.max_price + a.min_price) / 3);

Answer 14

select pi.uni_sale_price,pi.uni_sale_price, pi.uni_sale_price, '1', '1', sysdate, sysdate
from sc_tt_price pp, sc_tm_product_info pi
where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
and pi.uni_sale_price_flg = '1'
and pp.status in (1, 2)
and  pp.sc_tt_price_id=24396
结果不为空。。
是不是我sql写错了啊。。---制定了统一零售价的数据，价格表中终端销售价/最高/最低价和产品表中统一零售价不同的数据，更新成统一零售价。。
update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 );
                     
commit;

Answer 15

正式环境下还是这样。。
当我指定price_id不报错。。
不指定还是报max_price为null---制定了统一零售价的数据
update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
                      where p.sc_tt_price_id = 255946     当把where p.sc_tt_price_id = 255946  注释后，还是报max_price为null

Answer 16

你15楼的代码，我执行了，没有报错，SQL写法也是正确的
既然你说你正式区没问题，测试区有问题
说明你测试区有数据是空的，不然不会报无法将null值插入max_price
你看看测试区的有哪些 数据是空的
SELECT * FROM sc_tm_product_info WHERE  uni_sale_price IS NULL ;

Answer 17

哦 我晓得原因了，你更新了一些不需要更新的数据，也就是说你更新了不存在于sc_tt_price表中的数据
这样修改下：update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
where exists(SELECT 1 FROM sc_tt_price WHERE p.sc_tt_price_id = sc_tt_price_id )

Answer 18

update where
限定下要更新的数据,不然你会把所有的数据都更新,匹配不上的就被更新成了null

Answer 19

--上面写错了，where条件换成  sc_tm_product_info，如下：
update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
where exists(SELECT 1 FROM sc_tm_product_info WHERE p.sc_tm_product_info_id = sc_tm_product_info_id )

Answer 20

update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
                      --where p.sc_tt_price_id = 255991      
where exists(SELECT 1 FROM sc_tm_product_info WHERE p.sc_tm_product_info_id = sc_tm_product_info_id)
还是报max_price为null ....

Answer 21

--这样呢？
update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price,  p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
where exists(SELECT 1 from sc_tt_price a, sc_tm_product_info b 
WHERE a.sc_tm_product_info_id = b.sc_tm_product_info_id  
and p.sc_tt_price_id = a.sc_tt_price_id )

Answer 22

还是max_price 为null ...您辛苦了

Answer 23

主要看你需要更新什么样的资料？
把条件加全试试：
where exists(SELECT 1 from sc_tt_price a, sc_tm_product_info b 
from sc_tt_price pp, sc_tm_product_info pi
where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
and p.sc_tt_price_id = pp.sc_tt_price_id
)

Answer 24

---制定了统一零售价的数据
update sc_tt_price p set (p.client_retail_price, p.max_price, p.min_price, p.status, p.pos_delivery_status, p.valid_date, p.update_date) 
                               =( select pi.uni_sale_price, nvl(pi.uni_sale_price,0), nvl(pi.uni_sale_price,0), '1', '1', sysdate, sysdate
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                                  and pi.uni_sale_price_flg = '1'
                                  and pp.status in (1, 2)
                                  and p.sc_tt_price_id = pp.sc_tt_price_id
                                 )
                      --where p.sc_tt_price_id = 257260
                      --where p.sc_tt_price_id = 275577  
where exists(SELECT 1 from sc_tt_price pp, sc_tm_product_info pi
                    where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                    and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id
                    and pi.uni_sale_price_flg = '1'
                    and pp.status in (1, 2)
                    and p.sc_tt_price_id = pp.sc_tt_price_id
            )可以了～～～谢谢潘哥～～～
太兴奋了明天就要开门营业的。。

Answer 25

---merge into  merge into sc_tt_price p using (select pi.uni_sale_price, nvl(pi.uni_sale_price,0) max_price, nvl(pi.uni_sale_price,0) min_price, '1' status, '1' pos_delivery_status, sysdate valid_date, sysdate update_date
                                  from sc_tt_price pp, sc_tm_product_info pi
                                  where (pp.max_price <> pi.uni_sale_price or pp.min_price <> pi.uni_sale_price or pp.client_retail_price <> pi.uni_sale_price)
                                  and pp.sc_tm_product_info_id = pi.sc_tm_product_info_id and pp.status in (1, 2) and pi.uni_sale_price_flg = '1')  k
on (p.sc_tt_price_id = k.sc_tt_price_id)
update set p.client_retail_price=k.uni_sale_price ,
p.max_price=k.max_price, p.min_price=k.min_price,p.status=k.status,p.pos_delivery_status=k.pos_delivery_status,p.valid_date=k.valid_date,p.update_date=k.valid_date