Updating rows in table B based on related field in table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)
I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.
companyID int(10) pKey
cSerialID int(10) old legacy pkey
bAgencyID int(10) pkey
Table A has values for both companyID (unique key) and cSerialID.
Table B has values for bAgencyID (unique key) and cSerialID but companyID is empty.
I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.
I need a query that will update ALL rows.
Any help would be greatly appreciated.Thanks in advance