Copy row from same table in mysql but alter certain columns

Question

I need help with copying a row from the same table and editing certain columns to create a new row.

Example Table (yes I know there is not a primary key):

CREATE TABLE myTable (
    columnToNotChange JSON, -- this column does not change
    columnToNotChangeTwo VARCHAR(5), -- this column does not change either
    columnToChange BOOLEAN -- this column I want to change 
);

Say I have a row:

columnToNotChange = '{json_object: []}'
columnToNotChangeTwo = "const"
columnToChange = true

What I want to know is how do you copy the data from the columnToNotChange and ColumnToNotChangeTwo but alter the columnToChange to create a new row in the table, myTable.

I've heard of UPDATE FROM (SELECT) statements but haven't found any good documentation for MySQL on UPDATE FROM (SELECT) statements.

I have looked Generic Stackoverflow Question, MySQL Documentation on The UPDATE statement, and W3Schools SQL UPDATE Statement

I appreciate any help.

This looks useful https://stackoverflow.com/a/10371315/2310830 — RiggsFolly, Jan 17 '21 at 16:56
Simple `INSERT NewTable (Col1, COl2, Col3) SELECT old.Col1, old.Col2, Calculation FROM old`? — Charlieface, Jan 17 '21 at 17:08

score 1 · Accepted Answer · answered Jan 17 '21 at 17:19

assuming you want copy a rows with pk id = 1

you should use an INSERT SELECT
if you want a new row and null values all the columns except columnToNotChange , columnToNotChangeTwo

then

insert into myTable  ( columnToNotChange , columnToNotChangeTwo  )
select columnToNotChange , columnToNotChangeTwo 
from myTable  
where id = 1

if you want assign some value to the column columnToChange then assign at the 3th column then value you need

insert into myTable  ( columnToNotChange , columnToNotChangeTwo ,columnToChange   )
select columnToNotChange , columnToNotChangeTwo, 'THE VALUE YOU WANT FOR columnToChange'
from myTable  
where id = 1

Copy row from same table in mysql but alter certain columns

1 Answers1