Downgrade Delete Statement From 4.0 -> 3.32, Help Pls! 2

[RaffiqEddy]

Hi,

I want to delete all data from Table1 based on data from Table2.

So do as following:

DELETE Table1
FROM Table1
INNER JOIN Table2 ON 
Table1.StatusId = Table2.StatusId AND
Table1.ApproveById = Table2.ApproveById AND
Table1.EmpNo = Table2.EmpNo

Using MySQL 4.0 on my PC it works perfectly.

BUT the problem with my Server which using MySQL 3.32, it returns with an error.

Can someone help me to DOWNGRADE the query above from MySQL 4.0 --> 3.32

TIA

Regards.

 

[TonyGroves]

I think older versions of MySQL didn't support the INNER JOIN ... ON syntax.

Try using instead:
[tt]
DELETE Table1
FROM Table1,Table2
WHERE
Table1.StatusId = Table2.StatusId
AND Table1.ApproveById = Table2.ApproveById
AND Table1.EmpNo = Table2.EmpNo
[/tt]

 

[RaffiqEddy]

I have tried that, but still not working... I also do as following but still not working..

DELETE FROM Table1
USING Table1,Table2
WHERE
Table1.StatusId = Table2.StatusId AND
Table1.ApproveById = Table2.ApproveById AND
Table1.EmpNo = Table2.EmpNo

Anyone have other solution??

TIA

 

[TonyGroves]

What exactly is the error message?

 

[RaffiqEddy]

DELETE Table1
FROM Table1,Table2
WHERE
  Table1.StatusId = Table2.StatusId
  AND Table1.ApproveById = Table2.ApproveById
  AND Table1.EmpNo = Table2.EmpNo

#1064 - You have an error in your SQL syntax near 'Table1 FROM Table1, Table2 WHERE Table1.StatusId' at line 1

DELETE FROM Table1
USING Table1,Table2
WHERE
Table1.StatusId = Table2.StatusId AND
Table1.ApproveById = Table2.ApproveById AND
Table1.EmpNo = Table2.EmpNo

#1064 - You have an error in your SQL syntax near 'USING Table1,Table2 WHERE Table1.StatusId = Table2.' at line 1

 

[TonyGroves]

It seems that both these forms of DELETE were introduced in MySQL 4.0. See

http://dev.mysql.com/doc/mysql/en/delete.html

. Looks like you'll have to find another way to do it, such as doing a select to find the required records, then deleting each record individually.

 

[r937]

the best you can do is run a join query to produce a list of primary keys that qualify for deletion, then use the generated list in a DELETE statement

DELETE from Table1 where pkey in ( 123, 234, 345, 456, ... 937 )


r937.com | rudy.ca

 

[RaffiqEddy]

Oh well, I knew this will come...

BTW, a star for both of you, excellent helper...

Take care,

Regards.