Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Replacing field values that match another table

    Hello,

    First of all, please excuse my inferior knowledge...
    I am trying to clean up a database that has not exactly been created nicely An example would be that for certain fields the value is one from a list -- i would have used, and will now want to convert it to, the situation that there is to be a separate table with the possible values, which has a relation to the main table (as opposed to typing the same value every time).

    IOW, a query SELECT DISTINCT Field1, COUNT(*) FROM Table1 GROUP BY Field1 would generate something like Value1 - 1234; Value2 - 2345; Value3 - 3456...

    Now, to get these into a new table I have used SELECT DISTINCT Field1 INTO tblField1 FROM Table1. I added an ID field (autonumber) to this table and added a NewField1 (numeric) field to Table1. This NewField1 should be filled with tblField1.ID iff tblField1.Field1 matches Table1.Field1

    (I really hope I am making myself clear - English is not my native language...)

    So in short, I've created the new, separate table, but now i need to fill the main table with the ID from that new table. Does anyone have any pointers for me?

    thanks in advance,

    Chris

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    update table1
    inner 
      join tblField1 
        on table1
    .Field1 tblField1.Field1
       set table1
    .newfield1 tblField1.ID 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    2

    Thanks !!!

    Thanks a million, this did the trick perfectly...

    Chris

Posting Permissions

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