I have a database with a field which I want UNIQUE of NULL some fields repeated in a database which should be NULL or UNIQUE, so I have made this SQL to update them:
UPDATE tb_afiliats SET `afiliat_dni` = NULL WHERE afiliat_dni IN ( SELECT afiliat_id FROM tb_afiliats AS taula2 WHERE afiliat_dni IN (SELECT afiliat_dni FROM (SELECT afiliat_dni, count(*) AS suma FROM tb_afiliats AS taula3 WHERE afiliat_dni IS NOT NULL GROUP BY afiliat_dni ) AS taula WHERE suma>1 ))
But phpmyadmin returns: #1093 - You can't specify target table 'tb_afiliats' for update in FROM clause
I have checked the ( SELECT afiliat_id FROM tb_afiliats AS taula2 WHERE afiliat_dni IN (SELECT afiliat_dni FROM (SELECT afiliat_dni, count(*) AS suma FROM tb_afiliats AS taula3 WHERE afiliat_dni IS NOT NULL GROUP BY afiliat_dni ) AS taula WHERE suma>1 )) and it work correctly.
Any hint of where is the error or another way to do so?
Start Free Trial
