复制 - 使用MySQL INSERT INTO粘贴行

Question

I have a table that contains many rows, ordered by the field 'seq'. I have selected rows 6-9 and I want to copy and paste them on row 3 for example. For that I'd like to create an SQL query that does the following:

INSERT INTO my_table ( seq, field1, field2.... ) 
SELECT seq, field1,field2..
FROM my_table
WHERE id IN ( 234, 233,232 )

(id field is the auto increment field that identifies my selected rows).

Now - I managed to duplicate the rows into the table. What is missing is to correctly update the 'seq' field in the following manner :

1. In the pasted location (3) my rows should contain the values 3,4,5.
2. All the original rows in that location should be incremented by 3 so that the original row (seq=3) should now become (seq=6) and all rows move 3 rows down the table.

Can this be achieved with an SQL query ?

Answer 1

You can create a trigger which check for seq, if a sequence exists, it will update sequence numbers above it and will.

create table s_sequence 
(seq number(3),
 name varchar2(2000)
);

create or replace trigger s_seq_order
  before insert on s_sequence
  for each row
declare
  seq_exists varchar2(20);
begin
  begin
    select 1 into seq_exists from s_sequence where seq = :new.seq;
  exception
    when NO_DATA_FOUND then
      null;
  end;
  if seq_exists = '1' then
    update s_sequence set seq = seq + 1 where seq >= :new.seq;
  end if;
end;

inserting (seq, name) with (1, 'A') , (2, 'B') .. (5, 'E')

now insert (2, 'F')

But, I am not sure if this is an appropriate way to deal with order. but why put any data in table in order ?

PS : This code tested in oracle.