Copy row and change a small subset of columns?

Question

First, let me preface by saying that I'm aware of the INSERT/SELECT solution to similar problems (see here), but it is not applicable to my scenario.

I'm looking for a solution which does not require explicitly listing all columns, as these are unknown ahead of time. This query will be run on the same table in dozens of separate databases. There is a subset of common columns across all databases, but each has its own unique columns specific to just that database, as well.

So, is there a way to copy an entire row, change some known/constant subset of columns, and reinsert it without explicitly listing all columns? Would the only solution be dynamically constructing the query using DDL info gathered from the schema?

Yes, the only way to do this is with dynamic sql. However I would strongly suggest reviewing your data model if it is in your control. As it sounds like you have a pivoted parent child relationship that should be unpivoted. — SQLMason, Apr 17 '13 at 14:02
I'm a bit confused. At what point do you know *which* columns will need to be updated? I agree with @Dan that this will require dynamic SQL, and I agree even more that the data model sounds sub-optimal. — Aaron Bertrand, Apr 17 '13 at 14:06
The data model is sub-optimal, and is not under my control. I do know which columns need to be updated (it's a set of 5 or so columns which are common to all instances of the table). — FtDRbwLXw6, Apr 17 '13 at 14:10

RichardTheKiwi · Accepted Answer · 2013-04-17T14:36:55.773

1

NOTE: This answer is for SQL Server. The oracle tag was added to the question after this answer

I'm going to assume that your table has an IDENTITY column that is also the primary key, according to principles of good design. Let's also assume it does not have computed columns (or timestamps or any type that will require more manipulation). Let's finally assume that you know at least the name of this ID column, which is standard, e.g. "id".

You can use this sequence:

SELECT * INTO #tmp FROM tbl WHERE id = @copyfrom;
ALTER TABLE #tmp DROP COLUMN id;
UPDATE #tmp SET
   column1 = ...,
   column2 = ...,
   column3 = ...;  --- the subset of columns you want to change
INSERT tbl SELECT * FROM #tmp;

SQL Fiddle Demo

edited Apr 17 '13 at 14:36

answered Apr 17 '13 at 14:10

RichardTheKiwi

105,798
26
196
262

Having tested this method, I don't think Oracle allows the last statement. I get *ORA-00947: not enough values*. – FtDRbwLXw6 Apr 17 '13 at 14:34
The answer has TSQL written all over it. For Oracle, I think you'd need to keep all the columns (so no `drop column` business) but you'd also have to update the PK column(s) such that it won't cause a PK violation – RichardTheKiwi Apr 17 '13 at 14:38
1

Need to null out the id, not drop it. Dropping it then inserting trys to insert tablevalues-1. (Assuming oracle works the same as other platforms converting the explicit null into the new identity) – Pirion Apr 17 '13 at 14:39

score 1 · Answer 2 · answered Apr 17 '13 at 14:10

I think you can do what you want with a combination of create table as (or select into) and update.

So, create a copy of the table:

create table newTable as
    select *
    from oldTable

or, depending on your database:

select *
into newTable
from oldTable

Then use update to make your change:

update newTable
    set col2 = col1,
        code = (case when code = 'bad' then good else code end)

Copy row and change a small subset of columns?

2 Answers2

SQL Fiddle Demo

Linked