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

    Join Date
    May 2004
    Posts
    296
    Rep Power
    10

    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
    Posts
    1,927
    Rep Power
    378
    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