CODEWITHSUNDEEP
sqldatabaseoracle-database
WowBow
WowBow

Reputation: 7593

Is there a SQL Statement that allows me to copy and insert existing rows but with one column change?

You might not understood what I want to ask from the title but ,here is the explanation.

I have a data in Oracle database table. What I wanted to do is insert a new data to the table. This new data is based on the existing data but I have to change the value of one columns. So if I have 10 rows in the database after the insertion i will have 20 rows but the new 10 rows contain the same data except on of the columns is changed.

E.g table before insertion a new data

 Col1   Col2    Col3

  a       b       AA

  1       2       33

table after insertion a new data

 Col1   Col2    Col3

  a       b       **BB**

  1       2       **44**

Upvotes: 1

Views: 1051

Answers (2)

MatBailie
MatBailie

Reputation: 86706

Provided that you can encode what the new value should be; yes.

INSERT INTO
  myTable (
    Col1,
    Col2,
    Col3
  )
SELECT
  Col1,
  Col2,               -- This is a specific example based on your comment.
  Col3 + 6            -- This just adds 6 to the existing value, but any SQL
FROM                  -- could actually go here, such as a CASE statement...
  myTable

So, the question becomes; Do you have rules that you can implement in SQL for calculating the new value for Col3?


The rules could be something basic like...

CASE WHEN Col3 = 'AA' THEN '**BB**'
     WHEN Col3 = '33' THEN '**44**'
                      ELSE 'Unknown'
END,

Or you could have all the new values in another table and look them up using a join...

INSERT INTO
  myTable (
    Col1,
    Col2,
    Col3
  )
SELECT
  OldTable.Col1,
  OldTable.Col2,
  COALESCE(NewTable.Col3, 'Unknown')
FROM
  myTable     AS OldTable
LEFT JOIN
  lookup      AS NewTable
    ON  OldTable.Col1 = NewTable.Col1
    AND OldTable.Col2 = NewTable.Col2

Or a whole bunch of other options.

Upvotes: 4

Justin Cave
Justin Cave

Reputation: 231651

It will depend on how you determine how to change the data. How do you know, for example, that AA should become BB or that 33 should become 44?

Something like this will work for the two cases you posted. You can adapt it to whatever rule you want by changing the CASE statement to compute the new value differently.

INSERT INTO table_name( col1, col2, col3 )
  SELECT col1,
         col2,
         (CASE WHEN col3 = 'AA' 
                 THEN 'BB'
               WHEN col3 = '33'
                 THEN '44'
               ELSE null
            END)
    FROM table_name;

Upvotes: 4

Related Questions