使用SQL Server和PHP进行内部联接和更新

Question

I am trying to update two tables (REQUISITION & STOCK) and subtract two column from both the table by using inner join on SQL Server. I've tried this and it doesn't work out.

    <?php
    $tsql = "UPDATE [MRS].[dbo].[REQUISITION] A
             INNER JOIN [MRS].[dbo].[STOCK] B
             ON B.StockId = A.StockId 
             SET A.RequestStatus = 'APPROVED', 
                 A.ApprovedDate = CONVERT(VARCHAR(10),
                 GETDATE(),103), 
                 B.StockQuantity = B.StockQuantity - A.RequestQuantity
             WHERE RequestId = '$_GET[RequestId]'";

    $result = sqlsrv_query($conn, $tsql, array(), 
              array ("Scrollable" => SQLSRV_CURSOR_KEYSET));    

    header('Location:approval.php');
 ?>

Answer 1

You can't update multiple tables inside single UPDATE statement. The only way to do it is to execute two separate UPDATE statements inside a single transaction

BEGIN TRANSACTION

UPDATE A
SET A.RequestStatus = 'APPROVED', 
    A.ApprovedDate = CONVERT(VARCHAR(10), GETDATE(),103)
FROM [MRS].[dbo].[REQUISITION] A
WHERE RequestId = $_GET[RequestId]

UPDATE B
SET B.StockQuantity = B.StockQuantity - A.RequestQuantity
FROM [MRS].[dbo].[REQUISITION] A
INNER JOIN [MRS].[dbo].[STOCK] B ON B.StockId = A.StockId 
WHERE RequestId = $_GET[RequestId]

COMMIT