Question for SQL statment - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Question for SQL statment

Question for SQL statment# Database - 数据库

x*g2000-11-09 08:11

1 楼

table purchase_order(po_id,user)
table purchase_order_item(po_id,poi_id,item_id,number)
table stock(item_id,number_in_stock)
how to update the stock table to deduct the number_in_stock for a
purchase_order? (with one statment)
thanks
and which book or document is the best to learn SQL about these matters?

a*o2000-11-09 08:11

2 楼

if give me the po_id, then
update stock set number_in_stock = s.number_in_stock -
poi.number
from purchase_order_item poi inner join stock s
on s.item_id = poi.item_id
where poi.po_id = xxxxxx
this is ANSI SQL. If you use Oracle, replace inner join with
implicit join.
Any SQL book can help in your case.

【在 x****g 的大作中提到】

: table purchase_order(po_id,user)
: table purchase_order_item(po_id,poi_id,item_id,number)
: table stock(item_id,number_in_stock)
: how to update the stock table to deduct the number_in_stock for a
: purchase_order? (with one statment)
: thanks
: and which book or document is the best to learn SQL about these matters?

s*g2000-11-09 08:11

3 楼

What if we want to deduct all the number_in_stock of all the items in the
PO? Multiple items, rather than just one.
Thanks!

【在 a****o 的大作中提到】

: if give me the po_id, then
: update stock set number_in_stock = s.number_in_stock -
: poi.number
: from purchase_order_item poi inner join stock s
: on s.item_id = poi.item_id
: where poi.po_id = xxxxxx
: this is ANSI SQL. If you use Oracle, replace inner join with
: implicit join.
: Any SQL book can help in your case.

p*s2000-11-09 08:11

4 楼

I guess UPDATE doesn't work unless you can do this
UPDATE stock
SET number_in_stock =
number_in_stock - (SELECT count(po_id)
FROM purchase_order_item I,
purchase_order O
WHERE I.po_id = O.po_id
GROUP BY I.item_id)
However, I don't think nowadays SQL implementations can achieve this.
If we start f

【在 x****g 的大作中提到】

s*g2000-11-09 08:11

5 楼

What about the following query?
update stock
set number_in_stock = s.number_in_stock-poi.number
from purchase_order_item poi, stock s
where poi.item_id=s.item_id

【在 a****o 的大作中提到】

p*s2000-11-09 08:11

6 楼

^^^^^^^^^^
It depends on the meaning of this "poi.number", if this is
a derived attribute recording count of purchase_order for the item,
then the UPDATE statement works.

【在 s*****g 的大作中提到】

: What about the following query?
: update stock
: set number_in_stock = s.number_in_stock-poi.number
: from purchase_order_item poi, stock s
: where poi.item_id=s.item_id

a*o2000-11-09 08:11

7 楼

That's it.

【在 s*****g 的大作中提到】

: What about the following query?
: update stock
: set number_in_stock = s.number_in_stock-poi.number
: from purchase_order_item poi, stock s
: where poi.item_id=s.item_id

a*c2000-11-09 08:11

8 楼

You should consider the required query is used for one
order, not all the orders. Add another table purchase_order
into your update statement.

where po.po_id = poi.po_id
and poi.item_id=s.item_id

【在 a****o 的大作中提到】

: That's it.