blocks|key|1709415|text|对于特定的查询，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1709416|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'
+++++++++++++++++++++)|code-block|syntax|javascript|1709417|然而，我可能会猜想你是在一个更高的层次上循环。要设置所有这样的值，请尝试执行以下操作：|1709418|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
+++++++++++++++++++++)|1709419|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For your specific query, you can do:

<pre><code>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'
 )
</code></pre>

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

<pre><code>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
 )
</code></pre>

blocks|key|1922945|text|您可以使用多表UPDATE语法在purchaseOrder和itemsOrdered之间实现ANTI-JOIN|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1922946|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|code-block|syntax|javascript|1922947|entityMap^0|7|6|G|D|U|C|1A|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

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

<pre class="lang-sql prettyprint-override"><code>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
</code></pre>

blocks|key|1922986|text|因为MySQL不支持if+exists(*Your+condition*)+(*Write+your+query*)，所以你可以这样写来实现一个'if子句‘：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1922987|(*Write+your+insert+or+update+query*)+where+not+exists+(*Your+condition*)|code-block|syntax|javascript|1922988|entityMap^0|A|1C|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>(*Write your insert or update query*) where not exists (*Your condition*)
</code></pre>

blocks|key|1709523|text|您还可以使用以下查询检查记录是否存在，然后对其进行更新：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1709524|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|code-block|syntax|javascript|1709525|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|1923009|text|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)));|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1923010|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Select FROM t1
 WHERE s11 &gt; 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)));
</code></pre>

blocks|key|1709529|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1709530|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|1923103|text|sql+server2008之后提供了基于Merge对insert、update和delete进行操作的单条match语句，也允许你加入。下面的示例可能会对你有所帮助。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1923104|MERGE+Target+AS+T
USING+Source+AS+S
ON+(T.EmployeeID+=+S.EmployeeID)+
WHEN+NOT+MATCHED+BY+TARGET+AND+S.EmployeeName+LIKE+'S%25'+
++++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%25'
++++THEN+DELETE+
OUTPUT+$action,+inserted.*,+deleted.*;|code-block|syntax|javascript|1923105|像这样，你可以在一个语句中插入、更新和删除。|1923106|有关更多信息，请参阅https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx上的官方文档|1923107|entityMap|0|LINK|mutability|MUTABLE|url|https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx^0|L|5|0|0|0|A|1W|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

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

<pre><code>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.*;
</code></pre>

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

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

blocks|key|1923172|text|如果该表包含数百万条记录，则以下查询将快速执行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1923173|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|code-block|syntax|javascript|1923174|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|1923357|text|您可以声明一个变量，用于保存select查询返回的结果数。如果此变量大于0，则可以运行update语句|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1923358|++++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'++++++++|code-block|syntax|javascript|1923359|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code> Declare @ResultCount int
 SELECT @ResultCount = count(*) FROM itemsOrdered WHERE purchaseOrder_ID = '@purchaseOrdered_ID' AND status = 'PENDING' 
 If @ResultCount &gt; 0
UPDATE purchaseOrder SET purchaseOrder_status = 'COMPLETED' WHERE purchaseOrder_ID = '@purchaseOrder_ID' 
</code></pre>

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

I would like to do:

<pre><code>SELECT *
 FROM itemsOrdered
 WHERE purchaseOrder_ID = '@purchaseOrdered_ID'
 AND status = 'PENDING'
</code></pre>

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

<pre><code>UPDATE purchaseOrder
 SET purchaseOrder_status = 'COMPLETED'
 WHERE purchaseOrder_ID = '@purchaseOrder_ID'
</code></pre>

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

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

SQL数据库

数据库

MySQL

下面是我想在我的MySQL数据库上做的事情。我想做的是：SELECT *    FROM itemsOrdered    WHERE purchaseOrder_ID = '@purchaseOrdered_ID'        AND status = 'PENDING'如果不返回任何行(这可以通过if(dr.HasRows == false)实现)，我现在将在purchaseOrder数据库中

问如何在SQL字符串中放入'if子句‘？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL字符串中放入'if子句‘？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL字符串中放入'if子句‘？
EN