如何为此编写查询 [英] how to write query for this

问题描述

我有一个名为prod_inventory的表
像这样的列

I have a table called prod_inventory
with columns like this

prod-grp-id, prod-id ,qty, Warehouse-id
1              1      10     1
1              1      5      2 
2              2      30     1
2              2      25     2

现在我想将产品从一个仓库转移到另一个仓库
而不是应该更新仓库中的数量.

就像如果我将5个具有prod-grp-id = 1和prod_id = 1的产品从Warehouse-id 1转移到Warehouse ID 2一样，它应该更新Warehouse_Id = 1和qty =中的qty = qty-5(结果5)的数量Warehouse_id 2中的数量+5(结果10).

但是它也应该检查条件.如果我们要转移更多，则不行.的商品数量超过可用数量，则不应执行该交易.数量不应更新为负数

如何为此plz编写查询对我有帮助.

[edit]添加了代码块-OriginalGriff [/edit]

now i want to transfer products from one warehouse to another warehouse
than it should update the qty in warehouses.

like if i transfer 5 product having prod-grp-id=1 and prod_id=1 from warehouse-id 1 to warehouse id 2 than it should update the qty like qty=qty-5(result 5) in warehouse_Id=1 and qty=qty+5(results 10) in warehouse_id 2 .

But it should check for the condition too. if we are transferring more no. of products than the available than it should not perform the transaction.qty should not be updated to negative

how to write query for this plz help me.

[edit]Code block added - OriginalGriff[/edit]

推荐答案

您无法在一条语句中更新两个表或两个不同的行-您需要执行两个UPDATE语句，最好作为TRANSACTION的一部分.

我会创建一个存储过程来在事务中同时完成这两个任务:

You can''t update two tables or two different rows in one statement - you need to do two UPDATE statements, preferably as part of a TRANSACTION.

Personnaly, I would create a Stored Procedure to do them both within a transaction:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE MoveGoods
AS
BEGIN
	SET NOCOUNT ON;
    BEGIN TRANSACTION

    UPDATE prod_inventory SET qty = qty-5 WHERE prodId = 1 AND WarehouseId=1
    UPDATE prod_inventory SET qty = qty+5 WHERE prodId = 1 AND WarehouseId=2

    COMMIT
END
GO

您可以在其中使用if else语句

Hi ,

you can use if else statements in it

IF (SELECT  QTY FROM PROD_INVENTORY WHERE PROD_GRP_ID=@PGID and PRODUCT_ID=@PID AND WAREHOUSE_ID=@FWRID) < 

15 开始 //进行您的交易 END ELSE @ msg = ' 没有足够的商品';

15 BEGIN //Do your transaction END ELSE @msg='No Sufficient Goods';

这里@msg是out参数

我希望您知道sqlserver中的参数

Here @msg is out parameter

I hope you know out parameters in sqlserver

这篇关于如何为此编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！