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!

Answer 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.

Answer 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.

Answer 3

1. Create another table with 2 fixed records, e.g. TwoRec (vvid), with values 2 and 3

2. Do a cartesian join with TwoRec, i.e.

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

Answer 4

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

Answer 5

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