CODEWITHSUNDEEP
mysqlcopy
WhiteRau
WhiteRau

Reputation: 857

MySQL: trouble copying data from one table to another based on matching ids

i need to copy existing data from one table to another based on matching fields. this is not a wholesale column dupe.

how it should work is: if the id in asset matches the assetID in report, then copy the repEmail field from the report table into the rep_email field in the asset table. this is what i've built based on the MySQL docs for insert...select:

INSERT INTO asset (rep_email)
  SELECT report.repEmail
  FROM report WHERE report.assetID = asset.id

this throws: Error in query (1054): Unknown column 'asset.id' in 'where clause'.

i also tried this:

where safety_report.eNumber = asset_roster.id
insert into asset_roster (building_rep_email) 
select bldgRepEmail from safety_report;

but it errors on the first line. :(

i'm not sure how to modify this to eliminate the error and properly write the values across. it seems like it should be a simple operation... please advise.

NB: i do not have admin access to the tables. the reason for this seeming duplication has to do with permissions to the different tables. and even if not, i'd need to write foreign keys anyway...

Upvotes: 1

Views: 23

Answers (1)

forpas
forpas

Reputation: 164069

I think that you want to update the column rep_email of asset with values from the column repEmail of report:

UPDATE asset a
INNER JOIN report r ON r.assetID = a.id
SET a.rep_email = r.repEmail

Upvotes: 1

Related Questions