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

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?

Solution 1:

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:

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:

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:

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:

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