Link to home
Start Free TrialLog in
Avatar of chaoticz
chaoticz

asked on

Insert into table from same table

I have the following values in the same table, "things". And I want to add to this table the fields where run_id = '2' so that they are in run_id = '3'

"things table" before
run_id      column2      column3
2      horse      ion
2      joke      war
2      dog      cat
3      pig      computer
3      lost      dead
3      dune      funk

"things" table after executing sql:
run_id      column2      column3
2      horse      ion
2      joke      war
2      dog      cat
3      pig      computer
3      lost      dead
3      dune      funk
3      horse      ion
3      joke      war
3      dog      cat

What is the SQL to do this?
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

What database are you using, and how is the field run_id defined?

AW
Avatar of dazzled1
dazzled1

Something along the lines of

UPDATE table_name set run_id = 3 WHERE run_id = 2


Cheers :)
Hi,

insert into things
  select run_id+1, column2, column3 where run_id=2;

regards.
ASKER CERTIFIED SOLUTION
Avatar of vanekl
vanekl

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of chaoticz

ASKER

I am using MSSQL 2000

run_id = int(4) primary key
column2 = int(4) primary key

before:
run_id    column2    column3
2      1      horse
2      2      home
2      3      car
3      4      apple
3      5      peach
3      6      grape

after:
run_id    column2    column3
2      1      horse
2      2      home
2      3      car
3      1      horse
3      2      home
3      3      car
3      4      apple
3      5      peach
3      6      grape
so run_id is NOT set by a Sequence?

you have introduced quite a change with the column2 requirement that appears to require that the NEW rows have a LOWER Column2 value than the existing Column2 values for Run_id = 3, additionally requiring the the existing rows for run_id =3 have their values for column2 incremented, as well.  That GREATLY complicates the problem.

AW
Avatar of Lowfatspread
insert into Table
 (run_id,Column2,column3)
 Select 3,A.Column2,A.column3
    from Table as A
   Where A.run_id = 2
       and not exists (select B.run_id
                                from Table as B
                               Where B.run_id = 3
                                    and b.column2 = a.column2
                                    and b.column3 = a.column3)

assuming that you don't want duplicates
and that order of column2, column3 values isn't
important

hth