这是我想对MySQL数据库做的。
我想做的是:
SELECT *
FROM itemsOrdered
WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
AND status = 'PENDING'
如果不返回任何行，则可以通过If (dr。HasRows == false)，我现在将在purchaseOrder数据库中创建一个UPDATE:
UPDATE purchaseOrder
SET purchaseOrder_status = 'COMPLETED'
WHERE purchaseOrder_ID = '@purchaseOrder_ID'
我怎样才能缩短这个过程呢?
当前回答
在选择查询中，可以声明一个变量保存返回结果的数量。 然后，如果这个变量大于0，就可以运行update语句
Declare @ResultCount int
SELECT @ResultCount = count(*) FROM itemsOrdered WHERE purchaseOrder_ID = '@purchaseOrdered_ID' AND status = 'PENDING'
If @ResultCount > 0
UPDATE purchaseOrder SET purchaseOrder_status = 'COMPLETED' WHERE purchaseOrder_ID = '@purchaseOrder_ID'
其他回答
如果表包含数百万条记录，那么下面的查询将快速工作。
UPDATE PO
SET PO.purchaseOrder_status = 'COMPLETED'
FROM purchaseOrder PO
LEFT OUTER JOIN itemsOrdered IOD ON IOD.purchaseOrder_ID = PO.purchaseOrdered_ID and IOD.status = 'PENDING'
WHERE IOD.purchaseOrder_ID IS NULL
对于您的特定查询，您可以这样做:
UPDATE purchaseOrder
SET purchaseOrder_status = 'COMPLETED'
WHERE purchaseOrder_ID = '@purchaseOrder_ID' and
not exists (SELECT *
FROM itemsOrdered WHERE purchaseOrder_ID = '@purchaseOrdered_ID' AND status = 'PENDING'
)
但是，我可能会猜测您是在更高的级别上进行循环。要设置所有这些值，请尝试以下方法:
UPDATE purchaseOrder
SET purchaseOrder_status = 'COMPLETED'
WHERE not exists (SELECT 1
FROM itemsOrdered
WHERE itemsOrdered.purchaseOrder_ID = purchaseOrder.purchaseOrdered_ID AND
status = 'PENDING'
limit 1
)
您还可以使用以下查询来检查记录是否存在，然后更新它:
if not exists(select top 1 fromFROM itemsOrdered
WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
AND status = 'PENDING' )
Begin
UPDATE purchaseOrder
SET purchaseOrder_status = 'COMPLETED'
WHERE purchaseOrder_ID = '@purchaseOrder_ID
End
if not exists(select top 1 fromFROM itemsOrdered
WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
AND status = 'PENDING' )
Begin
UPDATE purchaseOrder
SET purchaseOrder_status = 'COMPLETED'
WHERE purchaseOrder_ID = '@purchaseOrder_ID
End
您可以使用多表UPDATE语法在purchaseOrder和itemsorordered之间实现ANTI-JOIN:
UPDATE purchaseOrder p LEFT JOIN itemsOrdered i
ON p.purchaseOrder_ID = i.purchaseOrder_ID
AND i.status = 'PENDING'
SET p.purchaseOrder_status = 'COMPLETED'
WHERE p.purchaseOrder_ID = '@purchaseOrder_ID'
AND i.purchaseOrder_ID IS NULL
