CODEWITHSUNDEEP
sqlsql-servert-sql
Curyous
Curyous

Reputation: 8866

How do you copy a row in a SQL table and alter one of the column values?

The answers for this question almost answer mine, but not quite. How do I turn this:

col0  col1 col2
data0 a    foo
data1 b    foo
data2 c    fee
data3 d    fee

into this? (duplicating the foo rows only)

col0  col1 col2
data0 a    foo
data1 b    foo
data2 c    fee
data3 d    fee
data0 a    bar
data1 b    bar

Where bar is from the statement, not the data, and the original table has 2 new rows.

Upvotes: 0

Views: 115

Answers (4)

sam yi
sam yi

Reputation: 4934

select col0, col1, col2
from yourtable
union all
select col0, col1, 'bar'
from yourtable
where col2 = 'foo'

Upvotes: 0

jtimperley
jtimperley

Reputation: 2544

Assuming you just wanted the results for these two hard-coded strings, the following query would provide that for you.

    SELECT
        col0,
        col1,
        'foo'
    FROM MyTable
UNION ALL
    SELECT
        col0,
        col1,
        'bar'
    FROM MyTable;

A more practical scenario is to use a temp table so you're not duplicating your query for each scenario.

CREATE TABLE #Options
(
    col2 VARCHAR(50)
);

INSERT INTO #Options VALUES ('foo'), ('bar');

SELECT
    col0,
    col1,
    #Options.col2
FROM MyTable
CROSS JOIN #Options;

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

insert into T (col0, col1, col2)
select col0, col1, 'bar'
from T

If by "copy" you mean a select then a union would work as in other answers or you could try this:

select col0, col1, case when num = 0 then col2 else 'bar' end as col2
from T, (select 0 as num union all select 1) as dup

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

One option, union all:

select col0, col1, col2
from yourtable
union all
select col0, col1, 'bar'
from yourtable

Upvotes: 0

Related Questions