CODEWITHSUNDEEP
mysql
menriquez
menriquez

Reputation: 249

How to copy an entire row from one table to another in mysql with the second table having a modified field

I'm working on a project where I need to import many rows with over 200 fields from an old table into a new, but the id field needs to have a "5" prepended to it.

I'm using this SQL statement:

INSERT INTO brinkoet_dev.property
SELECT liveprop.*
FROM brinkoet_live.property liveprop
WHERE listing_office='Brink';

but have no obvious way of being able to change an existing field's contents.

Upvotes: 0

Views: 105

Answers (2)

davey
davey

Reputation: 1791

In your new table rename the 'id' field to 'id_old'. In your new table, add an 'id' field at the end of this table. Then you can do the following:

INSERT INTO brinkoet_dev.property 
    SELECT liveprop.*, CONCAT(5, liveprop.id) 
    FROM brinkoet_live.property liveprop 
    WHERE listing_office='Brink';

After it's finished, just remove the id_old field from your new table.

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

INSERT INTO brinkoet_dev.property
  SELECT concat(5, liveprop.ID), field2, field3
  FROM brinkoet_live.property liveprop
  WHERE listing_office='Brink'

Where field2 and field3 are the rest of the fields of the liveprop table (without including the ID).

Here is a fiddle for you to play with.

Upvotes: 0

Related Questions