Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    63

    Unanswered: Temporary table trouble

    Hello there!

    I need your suggestions, dear community.

    I try to use a temporary table to remove duplicates from main table and compose such query:
    Code:
    create temporary table temp_comp
    select count(companyname), companyname, companyid from company
    group by companyname
    order by companyid;
    select * from temp_comp;
    delete from company c_m where c_m.companyid not in (select t.companyid as cid from temp_comp t);
    I get an error message trying this query to execute, it says I make mistake near of the (select t.companyid as cid from temp_comp t) clause. When I run it particularly it works fine. I use mysql version 4.0.16. Is it trouble of subquery using impossibility?

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    MySQL <= 4.0.x does not have subqueries.

    I don't think you can rewite that last query though as you can't do joins in deletes (apart from in a subquery.....and so on).

    Have a look at the manual:

    http://www.mysql.com/doc/en/Rewriting_subqueries.html

    Regards,

    Matt.

  3. #3
    Join Date
    Oct 2003
    Posts
    63
    I rewrote my query. So, simple select works fine, but when I try to deletion with such conditions I get an error. For example:

    Code:
    select * from company join tempt 
    on company.companyname = tempt.astring and company.companyid <> tempt.id
    works fine, but

    Code:
    delete from company join tempt 
    on company.companyname = tempt.astring and company.companyid <> tempt.id
    gives me a headache .

  4. #4
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    As I said in my first reply, you can't do joins in deletes (unless it's part of a select in a subquery, and as it's not 4.1, that makes it irrelevant).

    The linked page above gives a few options as to how you can get around the problem. By far the easiest way is to process the results of what would be your subquery in a scripting language then pass it back to a second query. The third method on that manual page is quite complex, but if you only have access to mysql command line tools, then that may be your only option.

    Regards,

    Matt.

  5. #5
    Join Date
    Oct 2003
    Posts
    63

  6. #6
    Join Date
    Nov 2003
    Posts
    3

    Re: Temporary table trouble

    I've used this syntax to create a temporary table:

    DROP TABLE IF EXIST tbl;
    CREATE TABLE tbl AS
    SELECT blah,blah FROM foo;

  7. #7
    Join Date
    Oct 2003
    Posts
    63

    Re: Temporary table trouble

    My question was solved - my mysql vrsion does'nt support subqueries

    Originally posted by SavX
    I've used this syntax to create a temporary table:

    DROP TABLE IF EXIST tbl;
    CREATE TABLE tbl AS
    SELECT blah,blah FROM foo;

  8. #8
    Join Date
    Oct 2003
    Posts
    63

    Re: Temporary table trouble

    My question was solved - my mysql version does'nt support subqueries

    Originally posted by SavX
    I've used this syntax to create a temporary table:

    DROP TABLE IF EXIST tbl;
    CREATE TABLE tbl AS
    SELECT blah,blah FROM foo;

Posting Permissions

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