Update Query issue for multiple rows

Question

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.

I'ts not smart to order by rowid - The rowid can change when you perform different operations on the table. How do you rank your data if not by rowid ? i'm guessing you want to rank by the time the row was created. do you have a update_date column in the table? — haki
– haki, Commented Jul 31, 2013 at 7:09

Emmanuel · Accepted Answer · 2013-07-31 07:30:45Z

1

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.

answered Jul 31, 2013 at 7:30

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

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

1 Comment

user2636867 Over a year ago

Thank you Emmanuel .Now its working after rplacing the "select * " with "Select MTB_I"

Collectives™ on Stack Overflow

Update Query issue for multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related