CODEWITHSUNDEEP
mysqlreplaceduplicates
Chloe
Chloe

Reputation: 25

MySql Duplicate a row and replace part of a field in the duplicate

I'm trying to duplicate a row in a mysql db and at the same time replace part of a string in one field of the duplicated row.

I've figured out how to:

  1. duplicate a row

    INSERT INTO account_external_ids 
SELECT * FROM account_external_ids 
where external_id like '%gerrit:%';

  2. do the replace of the string but cant figure out how to do both in the same query.

    UPDATE account_external_ids 
SET external_id = REPLACE(external_id,'gerrit:','username:') 
WHERE external_id like '%gerrit%';

But can't figure out how to do both in the same query, something like:

INSERT INTO account_external_id   
select * from account_external_ids 
set external_id = replace(external_id, 'gerrit:', 'username:') 
where external_id like '%gerrit%';

Any pointers would be great.

thanks

Upvotes: 2

Views: 170

Answers (1)

Barmar
Barmar

Reputation: 782158

You have to list all the columns explicitly, you can't use SELECT * if you're modifying any of the columns

INSERT INTO account_external_id (col1, col2, col3, col4, external_id)
SELECT col1, col2, col3, col4, REPLACE(external_id, 'gerrit:', 'username:')
FROM account_external_ids
WHERE external_id LIKE '%gerritt:%'

Upvotes: 1

Related Questions