OraFAQ Forum: SQL & PL/SQL » Insert Query in between rows - SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Insert Query in between rows - SQL (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert Query in between rows - SQL [message #630834]

Mon, 05 January 2015 11:25

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Guys can any one help me with below query where i need to insert two records by shifting the sequence number two ahead where sequence is having unique constraint,
For e.g. my table has records
xyz 1
zz 2
aa 3
bb 4
cc 5
I need to insert two records in between 3rd and 4th sequence

xyz 1
zz 2
aa 3
new 4
new_1 5
bb 6
cc 7

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630835 is a reply to message #630834]

Mon, 05 January 2015 11:37

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Update the rows you want/have to shift
2/ Insert the new rows.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

[Updated on: Mon, 05 January 2015 11:37]

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630837 is a reply to message #630835]

Mon, 05 January 2015 11:38

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Yeah thats what i did is it way to achieve in single query i some what tried with merge statement but not able to get the query

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630838 is a reply to message #630837]

Mon, 05 January 2015 11:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Did you read in the Posting Guidelines where we don't do homework assignments?

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630840 is a reply to message #630837]

Mon, 05 January 2015 11:56

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It may be possible but we have to check so you have to post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Please read the links I posted.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630842 is a reply to message #630840]

Mon, 05 January 2015 12:09

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Please find my table created, kindly ignore the data which i posted in original post

create table T(name, seq unique) as  
select chr(ascii('a')+level-1) name, level seq
from dual  
connect by level <= 5;

I need to insert a record between 3rd and 4th sequence

output

a	1
b	2
c	3
y	4
z	5
d	6
e	7

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630844 is a reply to message #630842]

Mon, 05 January 2015 12:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

explain how/why INSERT changes values in existing rows.

rows in a table are like balls in a basket.
There is no inherent row order in a heap table.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630846 is a reply to message #630844]

Mon, 05 January 2015 12:22

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Hi

Since I am inserting two records in between the sequence the existing sequence will be update which would shift two sequences ahead

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630847 is a reply to message #630842]

Mon, 05 January 2015 12:23

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

I looks as though you have to insert two rows and update two other rows. But which rows? I think you need to explain further what the rules for this operation are. For example, is either the column NAME or the column SEQ a primary key that can never be updated? If you can answer that question, then it will become clear which rows should be inserted and which rows should be updated.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630849 is a reply to message #630834]

Mon, 05 January 2015 12:28

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Hi

I would be updating the sequence which is primary key column and insert two records with new name And obviously the sequence numbers which will be update the exiting sequence in order to shift the existing name and sequence two sequences ahead. So ideally it's inserting Two records with name and sequence in between two records

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630850 is a reply to message #630842]

Mon, 05 January 2015 12:29

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not possible to do it with merge.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630851 is a reply to message #630849]

Mon, 05 January 2015 12:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I would be updating the sequence which is primary key column

What happens when FK exists which depends upon PK?

This whole problem is ameteurish & boderline nonsensical.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630852 is a reply to message #630849]

Mon, 05 January 2015 12:31

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I would be updating the sequence which is primary key column

You never update a primary key otherwise it is not a primary key.
And this is for this reason than you cannot do it with MERGE.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630853 is a reply to message #630851]

Mon, 05 January 2015 12:31

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

There is no foreign key

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630855 is a reply to message #630853]

Mon, 05 January 2015 12:34

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Any other away a part from merge and moreoevr I don't wanted to disable the constraint. I can achieve this by normal insert and update statements . Is there any other way

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630856 is a reply to message #630855]

Mon, 05 January 2015 12:38

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No.
Of course this can't be a real case.
As I said, in real world, you do not, you MUST not update a primary key.
If you do this in an application the design of this one is definitely flawed and BIG problems will come sooner or later.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630857 is a reply to message #630856]

Mon, 05 January 2015 12:40

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Ok what if I disable the key I mean if there Is a unique key on that column

[Updated on: Mon, 05 January 2015 12:42]

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630858 is a reply to message #630857]

Mon, 05 January 2015 12:45

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not more, the priblem is not in the existence of the PK in the database.
The primary key exists at MODEL level first, then you (and Oracle) implement it.
You cannot update it at MODEL, design level, then at implementation one. But at implementation level you can always do anything, you can delete your data , you can drop the database.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630859 is a reply to message #630858]

Mon, 05 January 2015 12:47

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Yes of course it's the implementation level only where this table act as a temporary table

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630860 is a reply to message #630859]

Mon, 05 January 2015 12:51

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post the complete description of your actual issue otherwise the answer is move the data into another temporary table.
Note that temporary tables are mostly not used in Oracle world and mainly misused.

Report message to a moderator

Re: Insert Query in between rows - SQL [message #630862 is a reply to message #630853]

Mon, 05 January 2015 14:21

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

rohit_shinez wrote on Mon, 05 January 2015 12:31

There is no foreign key

Not today anyway.
What about that requirement the application architect is going to walk in tomorrow morning?

Like others have said, if that value is part of your primary key, then you have poorly chosen a primary key.

Report message to a moderator

Previous Topic:	Update statement updates all rows
Next Topic:	Sql Querry based on condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 08:52:14 CDT 2024