如何在SQL字符串中放置"if子句"? [英] How do I put an 'if clause' in an SQL string?

问题描述

这就是我要在 MySQL 数据库上执行的操作.

So here's what I want to do on my MySQL database.

我想这样做:

SELECT *
    FROM itemsOrdered
    WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
        AND status = 'PENDING'

如果那样不会返回任何行，而这可以通过if(dr.HasRows == false)进行，那么我现在将在purchaseOrder数据库中创建一个UPDATE:

If that would not return any rows, which is possible through if(dr.HasRows == false), I would now create an UPDATE in the purchaseOrder database:

UPDATE purchaseOrder
    SET purchaseOrder_status = 'COMPLETED'
    WHERE purchaseOrder_ID = '@purchaseOrder_ID'

我如何使这个过程更短一些?

How would I be able to make this process a little shorter?

推荐答案

对于您的特定查询，您可以执行以下操作:

For your specific query, you can do:

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'
                     )

但是，我可能会猜测您正在循环播放更高级别的内容.要设置所有此类值，请尝试以下操作:

However, I might guess that you are looping at a higher level. To set all such values, try this:

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
                     )

这篇关于如何在SQL字符串中放置"if子句"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！