March 9, 2009 at 7:44 am
Hi ..
I have some problem in updating the DB when i use a rownum starting with > 1 ..
rownum between 1 and 5000 works..
but anything > than 1 as start point is not working ..
The query used is ...
UPDATE TIDLRREP t SET (t.translated_term_79, t.repository_status) =
(SELECT a.TRANSLATED_TRM_160,'40' FROM TIDLGGLS a
WHERE upper(a.base_language_term) = upper(t.base_language_term) AND
t.MAX_TERM_SIZE >= a.TRANS_TERM_SIZE )
where rownum between 1 and 5000;
This query updates in bulk.. The inner query returns around 80000 records..
I am trying to update the DB my paginating .. in intervals of say 5000 ..
so on trying ..
UPDATE TIDLRREP t SET (t.translated_term_79, t.repository_status) =
(SELECT a.TRANSLATED_TRM_160,'40' FROM TIDLGGLS a
WHERE upper(a.base_language_term) = upper(t.base_language_term) AND
t.MAX_TERM_SIZE >= a.TRANS_TERM_SIZE )
where rownum between 5001 and 10000;
No rows are updated ..
Please help...
March 9, 2009 at 8:12 am
Is rownum a column in one of the tables? If so, what's the range on it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 11:43 pm
rownum is not the column in the table..
its the index number .. or the row number of the result set i need to access ..
March 10, 2009 at 12:00 am
Can we write a update query like the way you have written
"UPDATE TIDLRREP t SET (t.translated_term_79, t.repository_status) =
(SELECT a.TRANSLATED_TRM_160,'40' FROM ..."
Are'nt you getting any errors?
"Keep Trying"
March 10, 2009 at 3:55 am
Yes.. U can ..
It is correlated sub query ..
if the inner sub query returns u multiple rows.. the update query actually loops thru the resultset and updates one by one..
it is very much possible..
March 10, 2009 at 7:17 am
manju_gallivant (3/9/2009)
rownum is not the column in the table..its the index number .. or the row number of the result set i need to access ..
If it's not a column in the query, how does it go into the Where clause? Are you sure this is MS SQL Server you're dealing with? There's no "rownum" function in SQL Server, so far as I can tell.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 6:40 am
manju_gallivant (3/10/2009)
Yes.. U can ..It is correlated sub query ..
if the inner sub query returns u multiple rows.. the update query actually loops thru the resultset and updates one by one..
it is very much possible..
Hey i still dont get it. Are you working on SQL Server ?
Am i missing something here?
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply