SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Deltona
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation 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.

    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

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ApeWare
    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:
    Code:
      Update TableB
      set companyID = TableA.companyID 
      from TableA A, TableB B
      Where A.cSerialID = B.cSerialID
    HTH
    Last edited by asterix; Jan 4, 2005 at 17:42.

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another possible Syntax might be:

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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by asterix
    I am unsure of mysql syntax, so here is some ANSI SQL:
    LOL

    like, that's a good strategy -- not

    Quote Originally Posted by asterix
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally, I prefer working syntax to sinful taxes any day of the week.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •