Mysql: How to Copy Rows, But Change a Few Fields

MySQL: How to copy rows, but change a few fields?

INSERT INTO Table
          ( Event_ID
          , col2
           ...
          )
     SELECT "155"
          , col2
           ...
      FROM Table WHERE Event_ID = "120"

Here, the col2, ... represent the remaining columns (the ones other than Event_ID) in your table.

How to copy multiple rows but change one field with mysql

Do you want a SELECT query, or an INSERT?

Here's a SELECT that should work for you:

select val, cols, cole
from your_table
UNION ALL
select 5 as val, cols, cole
from your_table
where val = 2

Here's an INSERT:

insert into your_table (val, cols, cole)
select 5 as val, cols, cole
from your_table
where val = 2

SQL: How to copy rows, and also change a fields(PK)

You have several Reserved Words in your column names:

Value, TimeStamp 

You need to enclose them with square brackets.

And if Order_nr is integer, don't use quotes around the value.

INSERT INTO Measurement_test ( BID, Order_nr, Parameter_ID, [Value], Machine_Serial_nr, [TimeStamp], Passed)
SELECT  BID, 12345, Parameter_ID, [Value], Machine_Serial_nr, [TimeStamp], Passed
FROM Measurement_test
WHERE BID = 123;

mysql copy complete row data from one table to another with different fields

You should use INSERT ... SELECT instead of INSERT ... VALUES and pass NULL for history_id:

INSERT INTO history_table
SELECT null, m.* 
FROM master_table m
WHERE m.id = 8;

See the demo.

Copy row with one field changed and new autogenerated id in MySQL

SELECT * is antipattern. You should explicitly set columns:

INSERT INTO original(col1, col2, ...)  --skip id column
SELECT col1, col2, ...
FROM temp1;

Or even better skip the temp1 table at all. Single statement solution:

INSERT INTO original(col1, col2, ..., field) -- skip id column
SELECT col1, col2, ..., 'new_value'
FROM original
WHERE field='old value';

---

To get what you want you need mechanism like Oracle DEFAULT Values On Explicit NULLs.

But even then you need to drop column from temp1, because:

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N+1 columns
FROM temp1;

So you have to use:

ALTER TABLE temp1 DROP COLUMN id;
-- now columns match

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N columns and
                      -- NULL is handled by DEFAULT ON NULL
FROM temp1;

Copy row and change a small subset of columns?

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

---

Related Topics