Update multiple rows in SQL

Question

I want to update all the document statuses to 9. How can I do this?

I did an update statement but it did not work - I do not know why.

SELECT
    sl.sap_filename,
    ds.document_status
FROM
    tbl_document ds,
    tbl_sap_log sl
WHERE
    ds.number = sl.number
    AND sap_id IN (44343, 55555, 66666, 77777, 88888) 
    AND ds.DOCUMENT_STATUS = 8;

Answer 1

If you want to update with 9 all the documents from your select :

UPDATE tbl_document SET tbl_document.DOCUMENT_STATUS = 9
WHERE tbl_document.DOCUMENT_STATUS = 8
AND EXISTS (SELECT 1
            FROM tbl_sap_log  
            WHERE tbl_sap_log.number  = tbl_document.number AND sap_id IN (44343,55555,66666,77777,88888) );

Answer 2

You could use MERGE:

MERGE INTO tbl_document ds
USING
    (SELECT number,
            sap_id
     FROM tbl_sap_log
     WHERE sap_id IN 
       (
        44343,
        55555,
        66666,
        77777,
        88888
       )
    ) sl
ON ( ds.number = sl.number )
WHEN MATCHED THEN
UPDATE SET
    ds.document_status = 9
WHERE ds.document_status = 8;