Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Join Date
    Jan 2007
    Location
    Los Angeles, California, United States
    Posts
    10,030
    Plugin Contributions
    32

    Default Trying to cleanup table.. Stuck.. Help..

    I'm using this script to locate records in my SBA table where the product no longer exists..

    Code:
    SELECT * 
    FROM   zen_products_with_attributes_stock
    LEFT OUTER JOIN zen_products
      ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
      WHERE zen_products.products_id IS NULL
    I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..
    My Site - Zen Cart & WordPress integration specialist
    I don't answer support questions via PM. Post add-on support questions in the support thread. The question & the answer will benefit others with similar issues.

  2. #2
    Join Date
    Jul 2012
    Posts
    14,769
    Plugin Contributions
    17

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by DivaVocals View Post
    I'm using this script to locate records in my SBA table where the product no longer exists..

    Code:
    SELECT * 
    FROM   zen_products_with_attributes_stock
    LEFT OUTER JOIN zen_products
      ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
      WHERE zen_products.products_id IS NULL
    I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..
    This work?
    Code:
    delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;
    Left off the prefix of your table(s), but....
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  3. #3
    Join Date
    Jul 2012
    Posts
    14,769
    Plugin Contributions
    17

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by DivaVocals View Post
    I'm using this script to locate records in my SBA table where the product no longer exists..

    Code:
    SELECT * 
    FROM   zen_products_with_attributes_stock
    LEFT OUTER JOIN zen_products
      ON (zen_products.products_id = zen_products_with_attributes_stock.products_id)
      WHERE zen_products.products_id IS NULL
    I have validated that these products no longer exist.. so I'm confident that these records can be deleted from the SBA table.. I need to know how to delete these records.. Kinda stumped on how to do this.. Hoping smarter brains than mine can help here..
    Or using your specific results of your query.

    Code:
    Delete from products_with_attributes_stock pwas
    Where pwas.products_id in (
    SELECT UNIQUE pwas1.products_id 
    FROM   products_with_attributes_stock pwas1
    LEFT OUTER JOIN products p
      ON (p.products_id = pwas1.products_id)
      WHERE p.products_id IS NULL
    )
    Though I think this later version is a bit more consuming than the earlier... Ideally left inner joins should be performed rather than outer joins... The first version does that (and ought to provide you the same results as what you posted), this second uses your outer join method. I threw in the unique statement just to be sure, probably doesn't need it.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  4. #4
    Join Date
    Jan 2007
    Location
    Los Angeles, California, United States
    Posts
    10,030
    Plugin Contributions
    32

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by mc12345678 View Post
    This work?
    Code:
    delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;
    Left off the prefix of your table(s), but....
    Nope.. here's the result..
    1 queries executed, 1 success, 0 errors, 0 warnings

    Query: delete pwas from zen_products_with_attributes_stock pwas left join zen_products p on p.products_id = pwas.products_id where p.pr...

    0 row(s) affected

    Execution Time : 0.094 sec
    Transfer Time : 0.001 sec
    Total Time : 0.096 sec
    My Site - Zen Cart & WordPress integration specialist
    I don't answer support questions via PM. Post add-on support questions in the support thread. The question & the answer will benefit others with similar issues.

  5. #5
    Join Date
    Jan 2007
    Location
    Los Angeles, California, United States
    Posts
    10,030
    Plugin Contributions
    32

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by mc12345678 View Post
    Or using your specific results of your query.

    Code:
    Delete from products_with_attributes_stock pwas
    Where pwas.products_id in (
    SELECT UNIQUE pwas1.products_id 
    FROM   products_with_attributes_stock pwas1
    LEFT OUTER JOIN products p
      ON (p.products_id = pwas1.products_id)
      WHERE p.products_id IS NULL
    )
    Though I think this later version is a bit more consuming than the earlier... Ideally left inner joins should be performed rather than outer joins... The first version does that (and ought to provide you the same results as what you posted), this second uses your outer join method. I threw in the unique statement just to be sure, probably doesn't need it.

    and this doesn't work either..

    1 queries executed, 0 success, 1 errors, 0 warnings

    Query: Delete from zen_products_with_attributes_stock pwas Where pwas.products_id in ( SELECT UNIQUE pwas1.products_id FROM zen_product...

    Error Code: 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pwas
    Where pwas.products_id in (
    SELECT UNIQUE pwas1.products_id
    FROM zen_pro' at line 1

    Execution Time : 0 sec
    Transfer Time : 0 sec
    Total Time : 0.664 sec
    My Site - Zen Cart & WordPress integration specialist
    I don't answer support questions via PM. Post add-on support questions in the support thread. The question & the answer will benefit others with similar issues.

  6. #6
    Join Date
    Jul 2012
    Posts
    14,769
    Plugin Contributions
    17

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by mc12345678 View Post
    This work?
    Code:
    delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id = null;
    Left off the prefix of your table(s), but....
    Oops...

    Used equals symbol instead of is assignment:

    Code:
    delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id IS null;
    Last edited by mc12345678; 29 Mar 2015 at 04:48 PM.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  7. #7
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    69,474
    Plugin Contributions
    6

    Default Re: Trying to cleanup table.. Stuck.. Help..

    What comes up when you run this?
    Code:
    SELECT * 
    FROM zen_products_with_attributes_stock 
    WHERE zen_products_with_attributes_stock.products_id NOT IN (SELECT zen_products.products_id FROM zen_products);
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.5]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...
    Signup for our Announcements Forums to stay up to date on important changes and updates!

  8. #8
    Join Date
    Jul 2012
    Posts
    14,769
    Plugin Contributions
    17

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by Ajeh View Post
    What comes up when you run this?
    Code:
    SELECT * 
    FROM zen_products_with_attributes_stock 
    WHERE zen_products_with_attributes_stock.products_id NOT IN (SELECT zen_products.products_id FROM zen_products);
    I came up with the same results as the previous select statement(s) (ie. in the sample I've created, two entries for products_id each with different data, both entries are returned and can also be deleted). Have seen that there are a number of ways to analyze/obtain the desired result(s)

    Not exists is another form..

    Code:
    select *
    FROM zen_products_with_attributes_stock
    Where not exists (Select Null from zen_products
    where zen_products.products_id = zen_products_with_attributes_stock.products_id);
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  9. #9
    Join Date
    Jan 2007
    Location
    Los Angeles, California, United States
    Posts
    10,030
    Plugin Contributions
    32

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by mc12345678 View Post
    Oops...

    Used equals symbol instead of is assignment:

    Code:
    delete pwas from products_with_attributes_stock pwas left join products p on p.products_id = pwas.products_id where p.products_id IS null;
    BAM!! That did it.. Makes perfect sense why it failed the first time.. I MIGHT have spotted the = vs IS too, but I was caffeine deprived and cranky..

    Thanks!!
    My Site - Zen Cart & WordPress integration specialist
    I don't answer support questions via PM. Post add-on support questions in the support thread. The question & the answer will benefit others with similar issues.

  10. #10
    Join Date
    Jul 2012
    Posts
    14,769
    Plugin Contributions
    17

    Default Re: Trying to cleanup table.. Stuck.. Help..

    Quote Originally Posted by DivaVocals View Post
    BAM!! That did it.. Makes perfect sense why it failed the first time.. I MIGHT have spotted the = vs IS too, but I was caffeine deprived and cranky..

    Thanks!!
    I'll just say that I wasn't fully engaged either... Gave it a shot without testing..

    If you haven't considered it further, (obviously) Ajeh's solution looks cleaner as it uses a bit more of sensible lexicon: Hey here's a list of things on the right, now on the left show me the things in the left that are not in the right...

    I don't know the details of when this action is to be performed (deletion of "extra" product entries) (as in multiple times in execution) or how many such entries are to be removed, or the overall load on the system for any of the above three functional methods, but, if infrequently run, whatever works and accomplishes the desired task. :)
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. Trying to setup Linkpoint and I'm stuck!
    By kathi247 in forum Built-in Shipping and Payment Modules
    Replies: 1
    Last Post: 10 Jul 2010, 03:30 PM
  2. STUCK!!.... trying to change login page
    By Shane78 in forum General Questions
    Replies: 4
    Last Post: 20 Sep 2009, 11:10 PM
  3. Database upgrade stuck at 1.3.0.2 trying to get to 1.3.8
    By websokind in forum Upgrading from 1.3.x to 1.3.9
    Replies: 1
    Last Post: 12 Dec 2007, 04:11 AM
  4. I am stuck trying to move catalog
    By Muddler in forum General Questions
    Replies: 4
    Last Post: 16 Jun 2006, 10:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR