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?
LVL 1
chaoticzAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vaneklConnect With a Mentor Commented:
you could try this,

insert into things (run_id,column2,column3) select 3, column2, column3 from things where run_id = 2;
0
 
Arthur_WoodCommented:
What database are you using, and how is the field run_id defined?

AW
0
 
dazzled1Commented:
Something along the lines of

UPDATE table_name set run_id = 3 WHERE run_id = 2


Cheers :)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
musduCommented:
Hi,

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

regards.
0
 
chaoticzAuthor Commented:
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
0
 
Arthur_WoodCommented:
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
0
 
LowfatspreadCommented:
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  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.