CODEWITHSUNDEEP
oracle-database
peter
peter

Reputation: 8652

How to overcome IN clause issue in oracle

i am updating a table called table2 from table1 on column name Active_status.But the problem the result contains more than 1000.How to overcome this issue.I am using this query in a c# application.I heard we can use temp table ,if so how can i use or is there any better way.followingis my query

UPDATE table2
SET Active_status = 'N',
 MODIFIED_DATE    = SYSDATE
    WHERE t2_SLNO    IN
      (SELECT t2_SLNO
      FROM table2
      LEFT JOIN table1
      ON table2.t2_NAME       = table1.t1_NAME
      WHERE table1.t1_NAME IS NULL
      )
    AND Active_status <> 'N';

EDIT I am doing this modification everday.By using a merge.But there are situations like table2 contain some dead data which i want to make status 'N' since the source of table1 and table2 is a remote database which is exposed by some other team.So thought of to do this work after my existing MERGE operation

Upvotes: 0

Views: 82

Answers (1)

sagi
sagi

Reputation: 40481

As @aleksej suggested, you can use MERGE:

MERGE INTO table1 t
USING table2 s
ON(t.t1_NAME = s.t2_NAME)
WHEN NOT MATCHED UPDATE SET Active_status = 'N',
                            MODIFIED_DATE = sysdate 
WHERE t.Active_status <> 'N'

And you can change you update to work like this:

UPDATE table1 t
SET t.Active_status = 'N',
    t.MODIFIED_DATE = sysdate
WHERE NOT EXISTS(SELECT 1 FROM table2 s
                 WHERE t.t1_NAME = s.t2_NAME)
  AND t.Active_status <> 'N'

Upvotes: 1

Related Questions