#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    4
    Rep Power
    0

    simple query help?


    Hi,

    I hope this is an ok place to ask a very basic question, as I find myself in a position to need to write queries suddenly with no training or experience writing queries.

    What I am trying to accomplish is this: A new pricing spreadsheet came from the manufacturer, I imported that info into an sql table in the database. Now I want to write a query that will look at my new table, compare it to an existing table and update any new or changed information in the existing table.
    Below is what I have come up with so far, but I'm not even sure if I am on the right track. Any help at all would be appreciated.

    Update IV00101
    From Big_Boy2
    Where IV00101.ITEMNMBR = Big_Boy2.SKU
    where IV00101.GEDESC = BIG_BOY2.MANUFACTURER
    where IV00101.currcost = big_boy2.netprice
    where IV00101.stndcost = big_boy2.sellprice
    where IV00101.itemdesc = big_boy2.descript


    Thanks,

    dwan
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    4
    Rep Power
    0
    I am learning slowly but surely. If they want me to do this I am going to need a good tutorial. Can anyone suggest a good one at a decent price?

    With reguard to the above question, am i on the right track thinking I need to use an inner join statement?

    Thanks in advanca for any help!

    Dwan
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    --Insert
    Insert into currTable
    SELECT *
    FROM oldTable
    where oldTable.primaryKey NOT IN(SELECT currTable.primaryKey
    FROM currTable)

    --Update
    update currTable
    set currTable.col1 = newTable.col1
    where currTable.primaryKey = newTable.primaryKey

    --How are you handling deletes?
    Last edited by victorpendleton; January 13th, 2004 at 09:06 AM.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    4
    Rep Power
    0
    There should not be too many to delete. I am going to run a compare script and then just manually delte the few that turn up. Thank you so much for your help!
    dwan
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Did this SQL work for you?
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    4
    Rep Power
    0
    it almost worked! Insert did, Update did not. However I think I got it using this:

    update IV00101
    set IV00101.col1 = BIG_BOY2.col1

    From IV00101, BIG_BOY2
    where IV00101.primaryKey = BIG_BOY2.primaryKey



    but am just now getting ready to test. I will let you know how it goes.

    Thanks,

    dwan
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    12
    You won't really want to use the FROM statement in your update.

    You'll probably want something more along these lines:

    update IV00101
    set IV00101.col1 = BIG_BOY2.col1
    where IV00101.primaryKey = BIG_BOY2.primaryKey

    Good luck!

    -- Jill

IMN logo majestic logo threadwatch logo seochat tools logo