Results 1 to 8 of 8
  1. #1
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    I have two databases. I need to set a field in DatabaseA to 0 when the sku for that row is not found in DatabaseB.

    Here is what I have tried:
    UPDATE DatabaseA,DatabaseB SET DatabaseA.products_status='0' WHERE DatabaseA.sku<>DatabaseB.sku

    Everytime I try this, it sets every products_status field to 0, even though the sku's match in each databse.

    Any ideas? I must be making a simple mistake.

    Bsnrjones

  2. #2
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    The message above should read as follows- I realized it was confusing, but then it had been to long to edit it:

    I have two Tables in the same database. I need to set a field in TableA to 0 when the sku for that row is not found in TableB.

    Here is what I have tried:
    UPDATE TableA,TableB SET TableA.products_status='0' WHERE TableA.sku<>TableB.sku

    Everytime I try this, it sets every products_status field to 0, even though the sku's match in each databse.

    Any ideas? I must be making a simple mistake.

  3. #3
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    Two ways, are these products both on the different tables? Then you need to express the linking code and then if they don't match.

    Otherwise:

    UPDATE TABLEA LEFT JOIN TABLEB ON TABLEA.SKU= TABLEB.SKU
    SET TABLEA.SKU = 0 WHERE TABLEB.SKU IS NULL

    Depending on the sql implementation, that should be close.

    Chet

  4. #4
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    Yes, I have two tables. One with existing products, and one with current inventory available.

    If items in the existing products table to not match with products in the current invetory table, then I need to set them to "out of stock".

    Does that make more sense?

    Burke

  5. #5
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    This will do it for you then:

    UPDATE TABLEA LEFT JOIN TABLEB ON TABLEA.SKU= TABLEB.SKU
    SET TABLEA.SKU = 0 WHERE TABLEB.SKU IS NULL

    By the way, while joins are getting towards advanced topics that people shy away from - a great book for anyone who uses sql is: Joe Celko's SQL for Smarties: Advanced SQL Programming

    It is not as difficult as a book as it sounds but has great examples and practical solutions to common problems. You would be surprised how much can be done in sql statements versus having to code a loop and walking through the data.

    Chet

  6. #6
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    Thanks for your help Chet. I have been messing with it.

    What I really need to do is to set TableA.status=0 not TableA.sku=0

    I tried this, it joins the tables, but then when it gets to the next line, it says the variable TableA is not correct.

    You have put me on the right track, I will just keep working with it until I figure it out, of course any other ideas would be great!

  7. #7
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    4,423
    What software are you using? Mysql? Access?

  8. #8
    Full Member
    Join Date
    January 18th, 2005
    Posts
    270
    I am using Mysql. I finally figured it out. What you gave me should work, but not on my version of sql.

    I am running 4.0.15, and it only works on 4.0.4 or above.

    I have figured out a clunky work around for now.

    Thanks for all your help! The knowledge in these forums is amazing.

    Burke

  9. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Need help with MySQL query
    By mobilebadboy in forum Programming / Datafeeds / Tools
    Replies: 3
    Last Post: January 17th, 2007, 03:00 AM
  2. MySQL
    By Dirk in forum Midnight Cafe'
    Replies: 4
    Last Post: February 21st, 2004, 05:44 PM
  3. mysql sl-o-o-o-o-o-o-w
    By Heyder in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: December 20th, 2002, 06:47 PM
  4. MySQL Help!
    By Pete in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: April 29th, 2002, 02:44 AM

Posting Permissions

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