UPDATE SELECT #1093 Error

[PhoenixD]

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)

That's my statement. I have duplicate entries in my database with the only difference being the phone number. I'm trying to transfer the phone number into one of the duplicates, then I half have and half can create a DELETE statement that will remove the duplicates, but I retain the phone number.

The error I'm getting is:
#1093 - You can't specify target table 'rehabs' for update in FROM clause

And I know that's what I'm doing wrong, but I'm not sure how to get a work-around... probably something to do with the creation of temporary tables, but I have no idea.

Any help would be greatly appreciated.

Thanks,
Kerry

 

[mbrooks]

What version of MySQL are you running?

M. Brooks

http://mbrooks.info

 

[PhoenixD]

Sorry I didn't mentioned before: 5.0.45

 

[guelphdad]

Don't make things more complicated than they need to be. See this article on deleting duplicates.

 

[PhoenixD]

Ahh yes but my main objective isn't how to delete duplicate entries - I can do that. Its transfering the phone field of one entry into the phone2 field of the other entry, thereby keeping all the data and losing none.

-Kerry