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;

Does this answer your question? Update statement with inner join on Oracle — Makdous
– Makdous, Commented Apr 26, 2020 at 21:17
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Apr 27, 2020 at 4:21

Gabriel Durac · Accepted Answer · 2020-04-26 21:14:57Z

4

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

answered Apr 26, 2020 at 21:14

Gabriel Durac

2,7681 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Python P Over a year ago

The best answer i love it ,,,, thank you so much u save me :)

Lalit Kumar B · Accepted Answer · 2020-04-26 21:31:06Z

4

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;

answered Apr 26, 2020 at 21:31

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

2 Comments

Python P Over a year ago

ohhh it is very nice i like it really

Lalit Kumar B Over a year ago

@PythonP Please mark it as answered, will help others too.

Collectives™ on Stack Overflow

Update multiple rows in SQL

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related