Copy selected data and increment entry ID

Question

I have selected my data with;

SELECT * FROM item_temp WHERE name LIKE '%starter%' AND Inventory LIKE '600';

I want to duplicate my selected data (Not overwrite it) multiply "entry" of every item in the query by 10.

As an example, the "entry" of one item is: 51327. I want to make a copy of the item with an entry of 513270.

I have tried a few different methods but they've all resulted in errors and I feel like I'm at a brick wall.

Thanks in advance.

insert into item_temp SELECT entry*10, c2, c3 ... FROM item_temp WHERE name LIKE '%starter%' AND Inventory LIKE '600'; — jarlh, Jan 23 '19 at 15:50
Possible duplicate of [How can I insert values into a table, using a subquery with more than one result?](https://stackoverflow.com/questions/9692319/how-can-i-insert-values-into-a-table-using-a-subquery-with-more-than-one-result) — DDS, Jan 23 '19 at 15:59
Hi, this didn't help. My struggle is duplicating the selected data and then multiplying the "entry" column by 10. Apologises if I didn't clearly explain well enough. I was also looking to keep all the data in the same table; item_temp — Callum Kent, Jan 23 '19 at 16:14
@CallumKent it's useful to post your attempt and see which error you get. — EzLo, Jan 23 '19 at 16:37

DDS · Answer 1 · 2019-01-23T16:00:50.417

0

Use the INSERT INTO syntax

 INSERT INTO table_name
   <your query with same column order as table_name>;

Another option is making the destination table ex-novo with select ... into statement

SELECT * 
into new_table
FROM item_temp 
WHERE name LIKE '%starter%' 
AND Inventory LIKE '600';

edited Jan 23 '19 at 16:00

answered Jan 23 '19 at 15:53

DDS

2,340
16
34

score 0 · Accepted Answer · answered Jan 23 '19 at 15:56

0

Something like this:

select (it.entry * 10 + n) as entry, . . .  -- the rest of the columns go here
from (select 0 as n union all select 1 union all . . . select 9) n cross join
     item_temp it
where it.name LIKE '%starter%' AND it.Inventory LIKE '600' ;

answered Jan 23 '19 at 15:56

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jan 23 '19 at 16:36

Use INSERT INTO with a SELECT that does the multiplication you need. You will have to write all columns on for the inserting table.

INSERT INTO item_temp (
    entry
    -- , other columns
    )
SELECT
    T.entry * 10 AS entry
    -- , other columns
FROM 
    item_temp T
WHERE 
    name LIKE '%starter%' AND 
    Inventory LIKE '600';

Copy selected data and increment entry ID

3 Answers3