如何在SQL字符串中添加if从句?

时间:2022-03-25 07:12:35

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

这是我要在MySQL数据库上做的。

I would like to:

我想:

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

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:

如果它不返回任何行,这是可能通过If实现的。现在,我将在purchaseOrder数据库中创建一个更新:

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

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

我怎样才能使这个过程更短一些呢?

8 个解决方案

#1


298  

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
                     )

#2


44  

You can use the multiple-table UPDATE syntax to effect an ANTI-JOIN between purchaseOrder and itemsOrdered:

您可以使用多表更新语法来实现purchaseOrder和itemsorated之间的反连接:

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

#3


40  

Since MySQL doesn't support if exists(*Your condition*) (*Write your query*), you can achieve an 'if clause' by writing like this:

由于MySQL不支持if存在(*您的条件*)(*编写查询*),您可以通过如下方式实现“if子句”:

(*Write your insert or update query*) where not exists (*Your condition*)

#4


21  

You can also use the following query to check if the record exists and then update it:

您也可以使用以下查询检查记录是否存在,然后更新:

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

#5


17  

Select FROM t1
    WHERE s11 > ANY
        (SELECT col1,col2 FROM t2
            WHERE NOT EXISTS
                (SELECT * FROM t3
                    WHERE ROW(5*t2.s1,77)=
                        (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
                            (SELECT * FROM t5) AS t5)));

#6


9  

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

#7


5  

it may help you when you use Merge in sql to insert,update and delete based on match statement in sql server like example it helps you https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

它可以帮助您在sql server中使用Merge来插入、更新和删除匹配语句,比如example,它可以帮助您https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

#8


0  

If the table contains millions of records then the following query will work fast.

如果该表包含数百万条记录,那么下面的查询将快速工作。

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

#1


298  

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
                     )

#2


44  

You can use the multiple-table UPDATE syntax to effect an ANTI-JOIN between purchaseOrder and itemsOrdered:

您可以使用多表更新语法来实现purchaseOrder和itemsorated之间的反连接:

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

#3


40  

Since MySQL doesn't support if exists(*Your condition*) (*Write your query*), you can achieve an 'if clause' by writing like this:

由于MySQL不支持if存在(*您的条件*)(*编写查询*),您可以通过如下方式实现“if子句”:

(*Write your insert or update query*) where not exists (*Your condition*)

#4


21  

You can also use the following query to check if the record exists and then update it:

您也可以使用以下查询检查记录是否存在,然后更新:

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

#5


17  

Select FROM t1
    WHERE s11 > ANY
        (SELECT col1,col2 FROM t2
            WHERE NOT EXISTS
                (SELECT * FROM t3
                    WHERE ROW(5*t2.s1,77)=
                        (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
                            (SELECT * FROM t5) AS t5)));

#6


9  

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

#7


5  

it may help you when you use Merge in sql to insert,update and delete based on match statement in sql server like example it helps you https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

它可以帮助您在sql server中使用Merge来插入、更新和删除匹配语句,比如example,它可以帮助您https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

#8


0  

If the table contains millions of records then the following query will work fast.

如果该表包含数百万条记录,那么下面的查询将快速工作。

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