Temporary table trouble

khibinite · #1 (**permalink**) 11-20-03, 09:58

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?

Mincer · #2 (**permalink**) 11-20-03, 10:50

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.

khibinite · #3 (**permalink**) 11-20-03, 10:59

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

.

Mincer · #4 (**permalink**) 11-20-03, 11:17

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.

khibinite · #5 (**permalink**) 11-20-03, 11:26

Yes, I see I am obliged to perform my needs with PHP

SavX · #6 (**permalink**) 11-20-03, 11:58

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

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

khibinite · #7 (**permalink**) 11-20-03, 13:08

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

Quote:

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;

khibinite · #8 (**permalink**) 11-20-03, 13:09

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

Quote:

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;

Internet Services

Web Development

Digital Marketing

Consumer Tech