How do I put an 'if clause' in an SQL string?
MysqlSqlMysql Problem Overview
So here's what I want to do on my MySQL database.
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 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?
Mysql Solutions
Solution 1 - Mysql
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
)
Solution 2 - Mysql
You can use the multiple-table UPDATE syntax to effect an ANTI-JOIN between purchaseOrder and 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
Solution 3 - Mysql
Since MySQL doesn't support if exists(*Your condition*) (*Write your query*), you can achieve an 'if clause' by writing like this:
(*Write your insert or update query*) where not exists (*Your condition*)
Solution 4 - Mysql
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
Solution 5 - Mysql
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)));
Solution 6 - Mysql
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
Solution 7 - Mysql
after sql server 2008 provide Merge to insert,update and delete operation based on single match statement, also that allows you to join. below sample example might be helps you.
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
Solution 8 - Mysql
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
Solution 9 - Mysql
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
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'