asked on

Need help on update SQL.

Help me on this sql.

I have the two tables
batch_info
document_info

i need to update the STATUS = 'COMPLETED' IN Batch_info table
where the module name is = 'EXPORT' Based on Document_info table
where the release ='1' record.

Here is the example.

Batch_info

EXTERNALBATCHID,NEXTMODULE,NEXTSTATUS
36267,EXPORT,READY
36264,EXPORT,READY

Document_info
DOCGUID,RELEASED,EXTERNALBATCHID
{B7FE744A-DE4D-4b1d-AEFA-65C3F40B2608},1,36267
{CDAAD0C0-AF11-4b53-97CF-C00D62C50F98},1,36264

Regards,
Praveen

HainKurt

try

update batch_info
set STATUS = 'COMPLETED'
where modulename = 'EXPORT'
and EXTERNALBATCHID in (select EXTERNALBATCHID from Document_info where RELEASED=1)

Open in new window

PRAVEEN T

ASKER

Result should be Like this ::

Batch_Info Table

EXTERNALBATCHID,NEXTMODULE,NEXTSTATUS
36267,EXPORT,COMPLETED
36264,EXPORT,COMPLETED

HainKurt

or using merge statement

MERGE INTO batch_info b
     USING Document_info d
        ON (b.EXTERNALBATCHID = d.EXTERNALBATCHID)
WHEN MATCHED
THEN
  UPDATE SET STATUS = 'COMPLETED'
         WHERE b.NEXTMODULE = 'EXPORT' AND d.RELEASED = 1

Open in new window

* not tested

Oracle Merge
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PRAVEEN T

ASKER

Its working .. thank you