UPDATE SELECT not working (complex) Error: 1093

[Nyfael]

MySQL Version: 5.0.45

UPDATE rehabs
SET phone2 = (
SELECT t1.phone
FROM rehabs t1, rehabs t2
WHERE t1.name = t2.name
AND t1.address1 = t2.address1
AND t1.address2 = t2.address2
AND t1.city = t2.city
AND t1.zip = t2.zip
AND t1.url = t2.url
AND t1.phone <> t2.phone
AND t1.rehabID <> t2.rehabID)
WHERE EXISTS (
SELECT t1.phone FROM rehabs t1, rehabs t2
WHERE t1.name = t2.name
AND t1.address1 = t2.address1 
AND t1.address2 = t2.address2 
AND t1.city = t2.city 
AND t1.zip = t2.zip 
AND t1.url = t2.url 
AND t1.phone <> t2.phone 
AND t1.rehabID < t2.rehabID)

 

the rehabs table structure

CREATE TABLE `rehabs` (
  `rehabID` int(11) NOT NULL auto_increment,
  `name` varchar(75) collate latin1_general_ci NOT NULL,
  `address1` varchar(100) collate latin1_general_ci default NULL,
  `address2` varchar(100) collate latin1_general_ci default NULL,
  `city` varchar(75) collate latin1_general_ci default NULL,
  `state` varchar(20) collate latin1_general_ci default NULL,
  `zip` varchar(10) collate latin1_general_ci default NULL,
  `phone` varchar(50) collate latin1_general_ci default NULL,
  `phone2` varchar(50) collate latin1_general_ci default NULL,
  `url` varchar(150) collate latin1_general_ci default NULL,
  `primary_focus` varchar(100) collate latin1_general_ci default NULL,
  `isActive` tinyint(1) default '0',
  `datecreated` datetime NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`rehabID`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1218 ;

 

I have duplicate entries in my database (mass insert statements, probably end up around 20,000 entries). They are duplicate in every way except their phone number, which will be different. I created a second phone field which will be empty by default (never inserted into). I'm trying to find the duplicates, takes the phone number from one of them and put it in the second phone number of the other, than proceeding to delete all the duplicated entries. The code to delete is working - I just need to get the update code going.

 

This is the error I'm getting:

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

 

Any help would be greatly appreciated.

 

-Kerry

[Illusion]

you cannot update a table and select from the same table in a subquery.