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?
"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?
Something along the lines of
UPDATE table_name set run_id = 3 WHERE run_id = 2
Cheers :)
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.
insert into things
select run_id+1, column2, column3 where run_id=2;
regards.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
(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
AW