IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

regarding the use of insert in SQL query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Wed Nov 19, 2008 6:18 pm
Reply with quote

Requirement :

I have a table1 with column A and B, this table has 25 rows. Now I want to sort this table based on column B and fetch only the first 5 rows from it. I do not want to go for a cursor but want to do it in single insert statement. I tried using the query below. But it says INVALID KEYWORD "ORDER"

My query is


INSERT INTO TABLE1
SELECT *
FROM TABLE1
ORDER BY B
FETCH FIRST 5 ROWS ONLY
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Wed Nov 19, 2008 7:00 pm
Reply with quote

Hi NikhilMF10, may be I am missing out on the actual requirement.
Why do you wish to insert data from the same table from which you are fetching it?
The order/sorting comes into picture when you are selecting the data from the table and not while inserting.

Cris
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 19, 2008 7:12 pm
Reply with quote

if there is a unique index, possible dup entries will prohibit the update.

Basic syntax is that you can not put an ORDER BY on the Select sub-clause of an INSERT.
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Thu Nov 20, 2008 8:51 am
Reply with quote

Hi,


I even tried using another Table table2 in first line, but this doesn’t work either icon_sad.gif

Please let me know the solution.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Nov 20, 2008 8:59 am
Reply with quote

dbzTHEdinosauer wrote:
Basic syntax is that you can not put an ORDER BY on the Select sub-clause of an INSERT.

Nikhil,

Guess you missed the above one. It is not the order of tables causing the problem. Please explain what you are trying to achieve.
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Thu Nov 20, 2008 12:07 pm
Reply with quote

I actually want, of the total 25 rows in the table, i want to select only 5 rows based on column B in ascending order- delete the rest.

Example :

Table1 has 10 rows

A B
pady 10
dar 1
parth 9
scot 6
ham 4
tirk 8
scob 2
dan 13
Andy 16
San 11


Now what i want is as below the best 5(as in ascending)

A B

dar 1
scob 2
ham 4
scot 6
tirk 8

irrespective of i have it in same table or the other, since i have to use the above table of five to sellect a vaue from it randomly using a random function.
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Thu Nov 20, 2008 12:08 pm
Reply with quote

PLease consider A as column 1 and B as column 2.
Back to top
View user's profile Send private message
ashok_uddaraju

New User


Joined: 21 Feb 2007
Posts: 72
Location: US

PostPosted: Thu Nov 20, 2008 12:21 pm
Reply with quote

i have tried on this query and it worked for fetching the first 5 rows

SELECT *
FROM TABLE1
ORDER BY B
FETCH FIRST 5 ROWS ONLY

where B should be either the column name or column number.
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Thu Nov 20, 2008 12:24 pm
Reply with quote

Yes it works for sellecting, but it doesnt work for inserting.
I can insert using a cursor but i do not want to because of performance reasons.

I want to do it in one single insert statement.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Nov 20, 2008 12:32 pm
Reply with quote

Quote:
irrespective of i have it in same table or the other, since i have to use the above table of five to sellect a vaue from it randomly using a random function.

What did you mean by the above statement. It's still not clear what you're trying. insert/select/delete. You have mentioned all icon_rolleyes.gif . Are you just trying to filter out 5 rows from 25 rows and display in output, or do you want to physically delete some rows from the table?
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Thu Nov 20, 2008 12:51 pm
Reply with quote

Yes i want to filter out 5 rows only in a new table or the same table can be used as i do not need the rest of the rows.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Nov 20, 2008 9:01 pm
Reply with quote

Hello,

You need to focus on getting the result you need. . .

Quote:
I can insert using a cursor but i do not want to because of performance reasons.
Have you shown some major performance issue with this exact requirement? Forget the rumor that all cursors are bad. . .

I suggest that if you want to read the first 5 rows of the result and inisert them elsewhere using a cursor will make exactly no (or almost immeasurable) difference in performance.
Back to top
View user's profile Send private message
NikhilMF10

New User


Joined: 19 Nov 2008
Posts: 8
Location: Pune

PostPosted: Fri Nov 21, 2008 10:48 am
Reply with quote

5 rows is just an example.
i have to deal with thousands of rows.

please suggest any way of doing it using a single insert query. icon_sad.gif
Back to top
View user's profile Send private message
darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Fri Nov 21, 2008 11:51 am
Reply with quote

Hi Nik,
Your example should be representative of what you REALLY want to achieve.

If you have to insert thousands of rows, (even just 5), I don't think you can do that in one select-insert statement. Using a cursor to select each row and then inserting them is the usual way to go. I don't see anything wrong with doing that.

However, if you want to do MASS INSERT, you can do it with just a simple


Code:
INSERT INTO TABLE
SELECT *
FROM TABLE
FETCH FIRST 5 ROWS ONLY


You don't need the ORDER BY. Why? We insert data in the table to populate them. When we want to get the records in the ORDER, then we issue the ORDER BY when we do the select.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
Search our Forums:

Back to Top