MySQL 如何在 SQL 字符串中放置“if 子句”？

How do I put an 'if clause' in an SQL string?

提问by John Ernest Guadalupe

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

所以这就是我想在我的MySQL数据库上做的事情。

I would like to do:

我想要做：

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 UPDATEin the purchaseOrderdatabase:

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

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

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

我怎样才能使这个过程更短一点？

回答by Gordon Linoff

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
                     )

回答by eggyal

You can use the multiple-table UPDATEsyntax to effect an ANTI-JOINbetween purchaseOrderand itemsOrdered:

您可以使用多表UPDATE语法来实现ANTI-JOIN介于purchaseOrder和之间itemsOrdered：

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

回答by Ranjit Singh

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

由于 MySQL 不支持if exists(*Your condition*) (*Write your query*)，您可以通过这样编写来实现“if 子句”：

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

回答by Santosh Shirkar

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

回答by sikandar bakht syed

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

回答by CH Hamza Ahmad

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

回答by Lalji Dhameliya

after sql server 2008 provide Mergeto insert,update and delete operation based on single match statement, also that allows you to join. below sample example might be helps you.

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.*;

like this you can insert, update and delete in one statements.

像这样，您可以在一个语句中插入、更新和删除。

and for more information you can refer official documents on https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

有关更多信息，您可以参考https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx 上的官方文档

回答by Amit Prajapati

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

回答by Alireza

You can declare a variable holding the number of returned results on select query. You can then run the update statement if this variable is more than 0

您可以声明一个保存选择查询返回结果数量的变量。如果此变量大于 0，则可以运行更新语句

    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'