SQL 문자열에 'if 절'을 어떻게 넣습니까?

SQL 문자열에 'if 절'을 어떻게 넣습니까?

---

MySQL 데이터베이스 에서 수행하려는 작업은 다음과 같습니다 .

나는하고 싶다 :

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

즉 통해 가능한 모든 행을 반환하지 않을 경우 if(dr.HasRows == false), 나는 지금 만들 것 UPDATE의 purchaseOrder데이터베이스를 :

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

이 과정을 좀 더 짧게 만들려면 어떻게해야합니까?

---

특정 쿼리의 경우 다음을 수행 할 수 있습니다.

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

그러나, 나는 당신이 더 높은 수준에서 루핑하고 있다고 생각할 것입니다. 이러한 모든 값을 설정하려면 다음을 시도하십시오.

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
                     )

---

당신은 여러 테이블 사용할 수있는 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

---

MySQL은을 지원하지 않으므로 if exists(*Your condition*) (*Write your query*)다음과 같이 작성하여 'if 절'을 달성 할 수 있습니다.

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

---

다음 쿼리를 사용하여 레코드가 있는지 확인한 다음 업데이트 할 수도 있습니다.

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

---

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

---

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

---

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

---

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

참고URL : https://stackoverflow.com/questions/13991817/how-do-i-put-an-if-clause-in-an-sql-string