qwerty1204 Posted January 14, 2013 Report Posted January 14, 2013 [quote name='Samanyudu' timestamp='1358197472' post='1303107553'] Still same error ORA-00971: missing SET keyword [/quote] edho syntax error ayyi untadi baa..chusukoo
vizagpower Posted January 14, 2013 Report Posted January 14, 2013 [img]http://www.desigifs.com/sites/default/files/samantha.gif?1337104081[/img]telvadu
iTeachSAP Posted January 14, 2013 Report Posted January 14, 2013 [quote name='Samanyudu' timestamp='1358194965' post='1303107315'] Hi Bayyas ....evaraina deni ela cheyalo chepandi ..basically nadi Oracle kadhu ..but epudu Oka table lo konni chnages cheyali koncham help cheyandi...... \ Scenario : Oka table lo One million items (Item_id) unai ...so nenu em cheyalante ...a items ni 5 batches ga cheyali ..i mean 5 * 200 k items ITEM_ID TYPE So prati 200 k items ki TYPE field same undali ... Fisrt 200K items ki TYPE = P1 ani update cheyali 2nd 200K items ki TYPE= P2 3rd 200K items ki TYPE= P3 4th 200K items ki TYPE= P4 5th 200K items ki TYPE= P5 Please KOncham help cheyandi vayyas..... [/quote] asalu query oracle lo rayala ? leka sql server lo rayala .. adhi cheppu mama firstuu ..
ChampakDas Posted January 14, 2013 Report Posted January 14, 2013 naaku work avutundhi...try it once if it doesnt work try the below way...include begin tran...oka vella nuvu anukunnatu vachindhi antey COMMIT type chesi execute cheyi else ROLLBACK TRAN ani koti execute cheyi BEGIN TRAN UPDATE TABLE_ATGSKU SET PROCESS ='P2' WHERE ITEM_ID in (SELECT top 200000 ITEM_ID FROM TABLE_ATGSKU WHERE PROCESS not in ('P1')) [quote name='Samanyudu' timestamp='1358197472' post='1303107553'] Still same error ORA-00971: missing SET keyword [/quote]
Samanyudu Posted January 14, 2013 Author Report Posted January 14, 2013 [quote name='iTeachSAP' timestamp='1358198184' post='1303107609'] asalu query oracle lo rayala ? leka sql server lo rayala .. adhi cheppu mama firstuu .. [/quote] Oracle query ani title lo ne petta kadha bayya...Oracle lo ne rayali ...Toad lo
Samanyudu Posted January 14, 2013 Author Report Posted January 14, 2013 [quote name='GatisKandis' timestamp='1358198576' post='1303107649'] naaku work avutundhi...try it once if it doesnt work try the below way...include begin tran...oka vella nuvu anukunnatu vachindhi antey COMMIT type chesi execute cheyi else ROLLBACK TRAN ani koti execute cheyi BEGIN TRAN UPDATE TABLE_ATGSKU SET PROCESS ='P2' WHERE ITEM_ID in (SELECT top 200000 ITEM_ID FROM TABLE_ATGSKU WHERE PROCESS not in ('P1')) [/quote] Bayya.................Query Oracle lo rayali ...SQL LO Kadhu ......Anyways thanks for ur Patience
ChampakDas Posted January 14, 2013 Report Posted January 14, 2013 aa kopam nee medha kadhu endhuku pani cheyatledhu ani...adhi general ga rasa it should work for both....epudu echindhi pani cheyatledha? If not try below one BEGIN TRAN UPDATE TABLE_ATGSKU SET PROCESS ='P2' WHERE ITEM_ID in (SELECT ITEM_ID FROM TABLE_ATGSKU WHERE PROCESS not in ('P1') FETCH FIRST 200000 ROWS ONLY) [quote name='Samanyudu' timestamp='1358198826' post='1303107674'] Bayya.................Query Oracle lo rayali ...SQL LO Kadhu ......Anyways thanks for ur Patience [/quote]
iTeachSAP Posted January 14, 2013 Report Posted January 14, 2013 [quote name='Samanyudu' timestamp='1358194965' post='1303107315'] Hi Bayyas ....evaraina deni ela cheyalo chepandi ..basically nadi Oracle kadhu ..but epudu Oka table lo konni chnages cheyali koncham help cheyandi...... \ Scenario : Oka table lo One million items (Item_id) unai ...so nenu em cheyalante ...a items ni 5 batches ga cheyali ..i mean 5 * 200 k items ITEM_ID TYPE So prati 200 k items ki TYPE field same undali ... Fisrt 200K items ki TYPE = P1 ani update cheyali 2nd 200K items ki TYPE= P2 3rd 200K items ki TYPE= P3 4th 200K items ki TYPE= P4 5th 200K items ki TYPE= P5 Please KOncham help cheyandi vayyas..... [/quote] [color=#ff0000][b]Asalu first 200K rows ani ela detect chesthunav? Using item id number ? item id numbers consecutive gaa unnaya ? [/b][/color]
iTeachSAP Posted January 14, 2013 Report Posted January 14, 2013 Item ids consecutive gaa unte.. [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P1[/size][/color] [color=#0000ff][size=5]where itemid<=200000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P2[/size][/color] [color=#0000ff][size=5]where itemid>200000 and itemid<=400000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P3[/size][/color] [color=#0000ff][size=5]where itemid>400000 and itemid<=600000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P4[/size][/color] [color=#0000ff][size=5]where itemid>600000 and itemid<=800000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P5[/size][/color] [color=#0000ff][size=5]where itemid>800000;[/size][/color]
loveindia Posted January 14, 2013 Report Posted January 14, 2013 [quote name='Samanyudu' timestamp='1358194965' post='1303107315'] Hi Bayyas ....evaraina deni ela cheyalo chepandi ..basically nadi Oracle kadhu ..but epudu Oka table lo konni chnages cheyali koncham help cheyandi...... \ Scenario : Oka table lo One million items (Item_id) unai ...so nenu em cheyalante ...a items ni 5 batches ga cheyali ..i mean 5 * 200 k items ITEM_ID TYPE So prati 200 k items ki TYPE field same undali ... Fisrt 200K items ki TYPE = P1 ani update cheyali 2nd 200K items ki TYPE= P2 3rd 200K items ki TYPE= P3 4th 200K items ki TYPE= P4 5th 200K items ki TYPE= P5 Please KOncham help cheyandi vayyas..... [/quote] Item_id ki values untai and Type = NULL untada table lo at first? if so use this.. UPDATE t SET type='P1' WHERE item_id <( SELECT item_id FROM ( SELECT item_id,rank()over(ORDER BY item_id) rn FROM tablename) UPDATE t SET type='P2' WHERE item_id <( SELECT item_id FROM ( SELECT item_id,rank()over(ORDER BY item_id) rn FROM tablename) WHERE rn between 200000 and 400000 Ila 5 conditions raasukoo... Enjoy. Let me know if it works..
Samanyudu Posted January 14, 2013 Author Report Posted January 14, 2013 [quote name='iTeachSAP' timestamp='1358199246' post='1303107708'] Item ids consecutive gaa unte.. [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P1[/size][/color] [color=#0000ff][size=5]where itemid<=200000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P2[/size][/color] [color=#0000ff][size=5]where itemid>200000 and itemid<=400000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P3[/size][/color] [color=#0000ff][size=5]where itemid>400000 and itemid<=600000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P4[/size][/color] [color=#0000ff][size=5]where itemid>600000 and itemid<=800000;[/size][/color] [color=#0000ff][size=5]update <TABLE NAME>[/size][/color] [color=#0000ff][size=5]SET TypeFIELD=P5[/size][/color] [color=#0000ff][size=5]where itemid>800000;[/size][/color] [/quote] item id's Consecutive kadhu ..istam vachinatlu unai....... for ex : Item id's : 16325562 16571660 16571733 16339741 16339920 16340006 16340324 16340367 16340618
iTeachSAP Posted January 14, 2013 Report Posted January 14, 2013 [quote name='Samanyudu' timestamp='1358199722' post='1303107753'] item id's Consecutive kadhu ..istam vachinatlu unai....... for ex : Item id's : 16325562 16571660 16571733 16339741 16339920 16340006 16340324 16340367 16340618 [/quote] So first 200k ante .. first 200k rows ana ?
loveindia Posted January 14, 2013 Report Posted January 14, 2013 deeniki nenu aitey em chesta antey.. aa table ki oka column add chesta.. called RNUM.. then rank() use chesi ranking ista.. and then based on the rank, update chesta.. after that RNUM column ni drop chesta.. this is faster and better since you said there will be 1 million records.
Samanyudu Posted January 14, 2013 Author Report Posted January 14, 2013 [quote name='iTeachSAP' timestamp='1358199806' post='1303107761'] So first 200k ante .. first 200k rows ana ? [/quote] yES FIRST 200 K ane......
Samanyudu Posted January 14, 2013 Author Report Posted January 14, 2013 [quote name='loveindia' timestamp='1358199916' post='1303107776'] deeniki nenu aitey em chesta antey.. aa table ki oka column add chesta.. called RNUM.. then rank() use chesi ranking ista.. and then based on the rank, update chesta.. after that RNUM column ni drop chesta.. this is faster and better since you said there will be 1 million records. [/quote] RNUM create chesi ranking esta anav kadha ...adi ela chestaru ................
Recommended Posts