mysql: duplicate certain rows using SQL?

Question

I have a table that looks like this:

nid  vid  tid
1   2    3
2   2    4
3   2    4
4   2    3

I'd like to run some SQL that will take each one of those rows and create another based on it like so:

foreach row where vid=2, create a duplicate row where vid = 3

So I'll end up with this:

   nid  vid  tid
    1   2    3
    2   2    4
    3   2    4
    4   2    3
    1   3    3
    2   3    4
    3   3    4
    4   3    3

The table is not that big (less than 500 rows, I think) and this is a one-time thing (so the code does not need to be amazingly optimized or anything). Thanks!

knittl · Accepted Answer · 2009-09-07 17:26:19Z

2

you’ll probably want this:

INSERT INTO `table`(`nid`, `vid`,  `nid`)
SELECT `nid`, `vid`+1, `nid`
  FROM `table`
 WHERE `vid` = 2

it inserts all rows, with vid incremented by 1

answered Sep 7, 2009 at 17:26

knittl

269k59 gold badges339 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean · Accepted Answer · 2009-09-07 17:28:50Z

2

If you want a general solution (i.e. all values of vid, but adding one to each of them), this is just adapting dandres' answer:

INSERT INTO TheTable(nid, vid_incremented,  nid)
SELECT nid, vid + 1 as vid_incremented , nid
FROM TheTable

Edit: fixed to do arithmetic in select statement.

edited Sep 7, 2009 at 17:28

answered Sep 7, 2009 at 17:23

Sean

4,48028 silver badges22 bronze badges

3 Comments

David Andres Over a year ago

I don't think SQL will allow you to use arithmetic in the column listing...but good approach. You'll need to do that match in the SELECT statement itself.

OMG Ponies Over a year ago

@dandres: Yes, you can do arithmetic and even data type conversion within the SELECT clause.

Sean Over a year ago

@rexem, dandres' comment was referring to before the edit, where the addition was in the INTO column listing.

David Andres · Accepted Answer · 2009-09-07 17:20:49Z

1

Could you do the following:

INSERT INTO TheTable(nid, vid,  nid)
SELECT nid, 3, nid
FROM TheTable
WHERE vid = 2

Two columns appear to have the same name, but the above should work. It takes the set of Vid = 2 elements, inserts them again but using Vid = 3.

answered Sep 7, 2009 at 17:20

David Andres

31.9k7 gold badges48 silver badges36 bronze badges

1 Comment

Eileen Over a year ago

oops, you're right -- that last column should be tid. Thanks for the help!

Andrew G · Accepted Answer · 2009-09-07 17:23:22Z

1

Create another table with 2 fixed records, e.g. TwoRec (vvid), with values 2 and 3
Do a cartesian join with TwoRec, i.e.

SELECT TheTable.nid, TwoRec.vvid, TheTable.nid FROM TheTable, TwoRec WHERE TheTable.vid = 2;

answered Sep 7, 2009 at 17:23

Andrew G

2,5062 gold badges17 silver badges23 bronze badges

Comments

Eric · Accepted Answer · 2009-09-07 17:25:33Z

1

I'd create a temp table of all of the vids you want to use:

create temporary table Vids
(
   vid int
)

declare @vid int
declare @maxvid int

set @vid = 1
set @maxvid = 500

while @vid < @maxvid
begin
    insert into vids values(@vid)
    set @vid = @vid + 1
end

Then, I'd use that temp table to build out your other table:

select
    t2.id,
    t1.vid,
    t2.nid
into newtable
from
    vids t1
    cross join tbl t2
order by
    t1.vid,
    t2.id

answered Sep 7, 2009 at 17:25

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Collectives™ on Stack Overflow

mysql: duplicate certain rows using SQL?

5 Answers 5

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related