Updating rows in table B based on related field in table A

[ApeWare]

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.

Table A
---------
companyID int(10) pKey
cSerialID int(10) old legacy pkey

Table B
---------
bAgencyID int(10) pkey
companyID int(10)
cSerialID int(10)

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

[asterix]

V
Table A
---------
companyID int(10) pKey
cSerialID int(10) old legacy pkey

Table B
---------
bAgencyID int(10) pkey
companyID int(10)
cSerialID int(10)

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.

Which way did you try?
I am unsure of mysql syntax, so here is some ANSI SQL:

  Update TableB
  set companyID = TableA.companyID 
  from TableA A, TableB B
  Where A.cSerialID = B.cSerialID

HTH

[r937]

I am unsure of mysql syntax, so here is some ANSI SQL:

LOL

like, that's a good strategy -- not

Another possible Syntax might be:

mysql's multi-table update syntax is not the same as in other databases

UPDATE TableB,TableA
SET TableB.companyID=tableA.companyID
WHERE TableB.cSerialID=tableA.cSerialID

[asterix]

Another possible Syntax might be:

 Update TableB AS B
 inner join tableA AS A on A.cserialid=b.cserialid
 set b.companyid=a.companyid

HTH

[asterix]

That is sooo wrong.
Look:

Update TableA, TableB

That says, theoretically, update two tables. Which is patently impossible in a single update statement, since you can only ever modify one set.

Sorry for not learing non-standard SQL, on braindamaged implementations.

[r937]

you do not need to apologize, my roman-loving friend

that's just the way it is in mysql, and one should never hesitate to use syntax that works

[asterix]

Personally, I prefer working syntax to sinful taxes any day of the week.