这是我想对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'
我怎样才能缩短这个过程呢?
当前回答
如果表包含数百万条记录，那么下面的查询将快速工作。
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
其他回答
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
因为MySQL不支持if exists(*Your condition*) (*Write Your query*)，你可以通过这样写来实现一个'if子句':
(*Write your insert or update query*) where not exists (*Your condition*)
您可以使用多表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
sql server 2008之后提供了基于单个匹配语句的Merge插入、更新和删除操作，也允许您加入。下面的例子可能会对你有所帮助。
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
像这样，您可以在一条语句中插入、更新和删除。
要了解更多信息，你可以参考官方文件 https://technet.microsoft.com/en-us/library/bb522522 (v = sql.105) . aspx
