Oracle Query .....help Help....

[Samanyudu]

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.....

[150bryant]

you mean partiioning the table ?

[ChampakDas]

edhi raw and straight forward method.....crisp and short query kosam thinking

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P1'

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P2'
WHERE ITEM_ID <>'P1'


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P3'
WHERE ITEM_ID NOT IN ('P1,'P2')


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P4'
WHERE ITEM_ID NOT IN ('P1,'P2','P3')

[Samanyudu]

[quote name='GatisKandis' timestamp='1358195315' post='1303107355']
edhi raw and straight forward method.....crisp and short query kosam thinking

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P1'

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P2'
WHERE ITEM_ID <>'P1'


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P3'
WHERE ITEM_ID NOT IN ('P1,'P2')


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P4'
WHERE ITEM_ID NOT IN ('P1,'P2','P3')
[/quote]


Bayya na table ela undi



ITEM_ID
TYPE
First 200 k Items
P1
2 ND 200K Items
P2
3 rd 200k items
P3
4 th 200k items
P4
5[sup]th[/sup] 200k items
P5

[cherlapalli_jailer]

[quote name='GatisKandis' timestamp='1358195315' post='1303107355']
edhi raw and straight forward method.....crisp and short query kosam thinking

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P1'

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P2'
WHERE ITEM_ID <>'P1'


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P3'
WHERE ITEM_ID NOT IN ('P1,'P2')


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P4'
WHERE ITEM_ID NOT IN ('P1,'P2','P3')
[/quote]


This should work

[ChampakDas]

nenu rasina query will work but bold ki ardam enti....is there any criteria for that?
[quote name='Samanyudu' timestamp='1358195657' post='1303107401']
Bayya na table ela undi



ITEM_ID
TYPE
[size=7][b]First [/b][/size]200 k Items
P1
2 ND 200K Items
P2
3 rd 200k items
P3
4 th 200k items
P4
5[sup]th[/sup] 200k items
P5
[/quote]

[Samanyudu]

[quote name='GatisKandis' timestamp='1358195315' post='1303107355']
edhi raw and straight forward method.....crisp and short query kosam thinking

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P1'

UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P2'
WHERE ITEM_ID <>'P1'


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P3'
WHERE ITEM_ID NOT IN ('P1,'P2')


UPDATE TOP 200 k TABLENAME
SET ITEM_ID ='P4'
WHERE ITEM_ID NOT IN ('P1,'P2','P3')
[/quote]


Fisrt 200k items ki Type =P1 ani set chesesanu ...

So epudu next 200k items ki Type p2 ani petali

nuvvu echihna query prakaram

UPDATE TOP 200000 TABLE_ATGSKU
SET PROCESS ='P2'
WHERE PROCESS NOT IN ('P1')

Ela estunte tappu chupistundi ......

[ChampakDas]

error vastundha?
[quote name='Samanyudu' timestamp='1358196132' post='1303107450']
Fisrt 200k items ki Type =P1 ani set chesesanu ...

So epudu next 200k items ki Type p2 ani petali

nuvvu echihna query prakaram

UPDATE TOP 200000 TABLE_ATGSKU
SET PROCESS ='P2'
WHERE PROCESS NOT IN ('P1')

Ela estunte tappu chupistundi ......
[/quote]

[ChampakDas]

avutundhi dude did with an example for you

Queries used:

UPDATE TOP(3) batting
SET Homeruns =10


UPDATE TOP(3) batting
SET Homeruns =20
WHERE Homeruns <>(10)


Output:

A 10
A 10
A 10
A 20
A 20
B 20
B 39
B 42

[Samanyudu]

[quote name='GatisKandis' timestamp='1358196237' post='1303107463']
error vastundha?
[/quote]

Naku enduko query tappu ani pistundi bayya.....

Update top 200000 TABLE_ATGSKU edi correct antava......

Na table format enti nate two fields unai...Okati ITEM_ID ..inkoti PROCESS ..

ITEM_ID lo 1 million skus unia ..PROCESS Field emo kaali

nenu em cheyalante ....eee 1 million items ni 5 groups la divide cheyali ...so 200k items ki PROCESS field lo values insert cheyali.....
200K items ki PROCESS = P1 ani update cheyali
2nd 200K items ki PROCESS= P2
3rd 200K items ki PROCESS= P3
4th 200K items ki PROCESS= P4
5th 200K items ki PROCESS= P5

[Samanyudu]

[quote name='GatisKandis' timestamp='1358196492' post='1303107486']
avutundhi dude did with an example for you

Queries used:

UPDATE TOP(3) batting
SET Homeruns =10


UPDATE TOP(3) batting
SET Homeruns =20
WHERE Homeruns <>(10)


Output:

A 10
A 10
A 10
A 20
A 20
B 20
B 39
B 42
[/quote]

So nenu eche query correct ee na chudu....

UPDATE TOP(200000) TABLE_ATGSKU
SET PROCESS ='P2'
WHERE PROCESS <> (P1)

<> ante ?

[ChampakDas]

ok..I just want to reconfirm whether there is a specific order for that or not...cool
[quote name='Samanyudu' timestamp='1358196526' post='1303107493']
Naku enduko query tappu ani pistundi bayya.....

Update top 200000 TABLE_ATGSKU edi correct antava......

Na table format enti nate two fields unai...Okati ITEM_ID ..inkoti PROCESS ..

ITEM_ID lo 1 million skus unia ..PROCESS Field emo kaali

nenu em cheyalante ....[b]eee 1 million items ni 5 groups la divide cheyali .[/b]..so 200k items ki PROCESS field lo values insert cheyali.....
200K items ki PROCESS = P1 ani update cheyali
2nd 200K items ki PROCESS= P2
3rd 200K items ki PROCESS= P3
4th 200K items ki PROCESS= P4
5th 200K items ki PROCESS= P5
[/quote]

[Samanyudu]

[quote name='GatisKandis' timestamp='1358196846' post='1303107507']
ok..I just want to reconfirm whether there is a specific order for that or not...cool
[/quote]

there is no specific order for that bayya.....5 group divide cheyali values insert cheyali ...

[ChampakDas]

correct put P1 in quotations
WHERE PROCESS <> ('P1')

<> ante not equal to ani...integers ki aithe =(equal to), <>(not equal to) strings ki aithe IN, NOT IN vadhu tharu...no lose if we use <> for string also
[quote name='Samanyudu' timestamp='1358196714' post='1303107500']
So nenu eche query correct ee na chudu....

UPDATE TOP(200000) TABLE_ATGSKU
SET PROCESS ='P2'
WHERE PROCESS <> (P1)

<> ante ?
[/quote]

[Samanyudu]

[quote name='GatisKandis' timestamp='1358197016' post='1303107521']
correct put P1 in quotations
WHERE PROCESS <> ('P1')

<> ante not equal to ani...integers ki aithe =(equal to), <>(not equal to) strings ki aithe IN, NOT IN vadhu tharu...no lose if we use <> for string also
[/quote]

Still same error

ORA-00971: missing SET keyword