Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2006
    Posts
    8

    Unanswered: converting query from select to update or delete

    I use this query to check that data in my products table is current on my daily csv file.

    select t2.PRODCODE, t2.products_id, t2.products_quantity
    from products as t2
    left outer
    join CSV as t1
    on t1.PRODCODE = t2.PRODCODE
    where t1.PRODCODE is null

    This brings a list up. I find this very useful but I need to know how to convert this type of query to an update. Basically I want to be able to update products.products_status to 0 if it appears on this list or alternatively delete the rows appearing on this list. I have tried repalcing the the word select with update or delete but it doesnt seemt o work.

    Any pointers would be helpful

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could modify that query and use is as a sub select as your feeder to either update or delete.

    I think you need to think clearly about what the requireed outcomes are. I dont think you can update joined tables (I'm pretty sure only access/JET can do that) in MySQL.

    it slook sto me as if you may have 2 main queries suignt he same subselect
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2006
    Posts
    8
    Hi

    Thanks for getting back to me. I forgot to point out that I am very new to this. I only understand that very basics and even the query listed above is way beyone me.

    All I know is that at the moment running this query blings up a list of about 300 items that are no loner stocked that I either need to remove or change to a status of "0". At the moment I have to print off the list and manually edit each one using

    update products
    set products_status = 0
    where products_id = "input ID here"

    As you can imagine this is very time consuming and I am sure there must be an easier way,.

    Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    I dont think you can update joined tables (I'm pretty sure only access/JET can do that) in MySQL.
    sorry, but mysql's multiple-table update capabilities are far more comprehensive than those of access

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2006
    Posts
    8
    Quote Originally Posted by r937
    sorry, but mysql's multiple-table update capabilities are far more comprehensive than those of access

    Wow, do you live in every mySQL forum? lol

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, and forums for other database systems too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2006
    Posts
    8
    So is it possible to use this kind of join with an update? and is it possible to use aliases for the table in this way with an update?

  8. #8
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    To get you back on track again, the sub select that Healdem was referring to would be something like this.
    My appologies for not be able to test this code before posting it, so do not try this against a production database please. Eventhough I did not test the code, but I'm pretty sure this would do the job:

    Code:
    update  products
    set     products_status = 0
    where   prodcode in
    (       select  t2.PRODCODE
            from    products as t2
            left    outer join    CSV as t1
            on      t1.PRODCODE = t2.PRODCODE
            where   t1.PRODCODE is null )
    HTH ...

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  9. #9
    Join Date
    Dec 2006
    Posts
    8
    Hi this helps a lot. thanks


    So just to get my head around this a subselect is where you have a query as normal but looking at the data slreadyselected from another query. Is that correct?

    Thanks

  10. #10
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Yep, hence SUBselect

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  11. #11
    Join Date
    Dec 2006
    Posts
    8
    I have today tried this sub select query out.

    I this error

    #1093 - You can't specify target table 'products' for update in FROM clause

    I am guessing this has something to do with the fact that I am using an alias in the sub select part? I have tried moving a couple of things around but with no luck.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ollyno1uk
    I have today tried this sub select query out.
    please refresh our memories -- which query did you run?

    also, do you understand the error message you were given?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    I think ollyno1uk means my code ...

    From the MySQL 5.0 manual:
    You can use a subquery for assignment within an UPDATE statement because subqueries are
    legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot
    use the same table (in this case, table t1) for both the subquery's FROM clause and the update
    target.
    ... Hmm, thought you couldn't mention the updated columns in the subquery, turns out to be the complete table
    My mistake ... Anyway, you could solve this by making a temporary copy of the products table and refer to that one in the subquery.

    Let's say you created the temporpary products table and called it TempProd, the complete query would end up looking like this:
    Code:
    update  products
    set     products_status = 0
    where   prodcode in
    (       select  t2.PRODCODE
            from    TempProd as t2
            left    outer join    CSV as t1
            on      t1.PRODCODE = t2.PRODCODE
            where   t1.PRODCODE is null )
    ... and don't forget to get rid of the TempProd table after this update succeeded.
    Sorry for the incorrect code the first time ...

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  14. #14
    Join Date
    Dec 2006
    Posts
    8
    Hi

    Yes it was the query that Yveau01 gave me. I think i understood the error in so much that It was saying i could not reuse a table name that was in as an alias elswhere.

    Yveau:

    Thanks very much for the modified query. I will try to run this shortly.

  15. #15
    Join Date
    Dec 2006
    Posts
    8
    Hi again

    Yes this method worked fine.

    Is there a better way of achieving this without the need for copying the entire table?

    Thanks for all your help

Posting Permissions

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