CODEWITHSUNDEEP
oracle-databaseoracle11g
user2636867
user2636867

Reputation: 3

Update Query issue for multiple rows

I have below update query to set some values and controle the data flow.But i am getting error "Too many values" from the condtion(subquery)when i execute the bellow query.

UPDATE MTB       ----- TABLE NAME
SET MTB_EXTR_FLAG='N',
MTB_ALOC_PROCESS='DC1'
WHERE MTB_I IN      --- PRIMARY KEY
(
SELECT * FROM
(
SELECT MTB_I ,ROW_NUMBER() OVER (ORDER BY ROWID) AS RN
FROM MTB
)
WHERE RN BETWEEN 100 AND 500
)

Here my intension is selecting the different set up data per processing of one job. I want to set MTB_EXTR_FLAG='N',MTB_ALOC_PROCESS='DC1' each time before running of the job with different set of data.

Can someone please help me to resolve the error issue or propose different query.

Thank you.

Upvotes: 0

Views: 48

Answers (1)

Emmanuel
Emmanuel

Reputation: 14209

I think this is just a matter of number of columns not matching (2 - MTB_I and RN - instead of 1 - MTB_I):

UPDATE MTB       
SET MTB_EXTR_FLAG='N',
MTB_ALOC_PROCESS='DC1'
WHERE MTB_I IN      --- PRIMARY KEY
(
SELECT MTB_I FROM -- Else RN will be taken !!
(
SELECT MTB_I ,ROW_NUMBER() OVER (ORDER BY ROWID) AS RN
FROM MTB
)
WHERE RN BETWEEN 100 AND 500
)

You can't do where x in (...) with a subquery returning more fields than expected.

Upvotes: 1

Related Questions