update test set qty = qty - 1 where (location,sku,valid_date)
in (select location,sku,min(valid_date) from test
where location = 'chuwei' and item_code = '5678' group by location,sku)
以上语句在pb中用下列方式执行 为什么第一条记录没有update
Execute Immediate :as_sql Using atran_ex
------解决方案--------------------
你是想让相同的location,sku中，最小的valid_date那条记录中qty-1?
以下两种方法都行。
update test
set qty = qty - 1
from test t where valid_date =
(select min(valid_date) from test where location = t.location and sku = t.sku)
update test
set qty = qty - 1
from test t where not exists
(select 1 from test where location = t.location and sku = t.sku and valid_date < t.valid_date)
------解决方案--------------------
不好意思,上面把条件漏了.
你是想让相同的location,sku中，最小的valid_date那条记录中qty-1?
以下两种方法都行。
update test
set qty = qty - 1
from test t where location = 'chuwei' and item_code = '5678' and
valid_date = (select min(valid_date) from test where location = 'chuwei' and item_code = '5678' and location = t.location and sku = t.sku)
update test
set qty = qty - 1
from test t where location = 'chuwei' and item_code = '5678' and
not exists (select 1 from test where location = 'chuwei' and item_code = '5678' and location = t.location and sku = t.sku and valid_date < t.valid_date)
------解决方案--------------------
update test set qty = qty - 1
where item_code = '5678'
and valid_date=(
select min(valid_date) from test a
where a.location= location and a.sku=sku )
)