Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

regarding the use of insert in SQL query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: regarding the use of insert in SQL query
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    Post subject: Reply to: regarding the use of insert in SQL query
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    Post subject:
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    Post subject: Reply to: regarding the use of insert in SQL query
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: 2158
Location: @my desk

PostPosted: Thu Nov 20, 2008 8:59 am    Post subject:
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    Post subject: Reply to: regarding the use of insert in SQL query
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    Post subject:
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: 70
Location: bangalore

PostPosted: Thu Nov 20, 2008 12:21 pm    Post subject: Reply to: regarding the use of insert in SQL query
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    Post subject: Reply to: regarding the use of insert in SQL query
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: 2158
Location: @my desk

PostPosted: Thu Nov 20, 2008 12:32 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Nov 20, 2008 9:01 pm    Post subject:
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    Post subject: Reply to: regarding the use of insert in SQL query
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us