1. eLearning Web Developer
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2004
    Rep Power

    Question Data Feed Compare Insert Update

    I have an existing table that will be updated daily via an FTP file load. I am trying to wrap my head around the logic for:
    -Giving matching records an "active" status
    -Inserting new records
    -Updating the existing records if non-existent in the upload file

    Table A (existing DB table):
    id (INT, primary key)
    name (VARCHAR)
    status (VARCHAR, can be "active" or "inactive")

    Table B (file being uploaded)
    id (INT, primary key)
    name (VARCHAR)
    status (VARCHAR, "active")

    What I would like to achieve when Table B is uploaded:
    -Matching records: tablea.status = active
    -Record exists in Table B, but not in Table A: INSERT record into Table A
    -Record exists in Table A, but not in Table B: tablea.status=inactive

    Any ideas on how to accomplish are much appreciated; my DB experience is limited. Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Rep Power
    Assuming the default state for status is 'active' (so your 2nd condition records are 'active'), you can do it in two queries - perhaps wrapped up in a transaction.

    1. Set all records in table_a to 'inactive'

    2. INSERT... ON DUPLICATE etc.

IMN logo majestic logo threadwatch logo seochat tools logo