Hi all, I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same time consider only the records depending on the join with another table. Now, the strange thing is that the first query updated the most of records, but then 4 records are still there, and in fact executing again the same update provides me another substitution. What could be the reason?
db=> begin; BEGIN raydb=> update elementi_dettagliset codice = regexp_replace( upper(codice), '(IST)*TIF$', '') where id_elemento in ( select ed.id_elemento from elementi_dettagli ed, elementi e where ed.id_elemento = e.id_elemento and e.categoria = 'bozzetti' and ed.codice ~* '(IST)*TIF$' ); UPDATE 4679 db=> select ed.id_elemento,ed.codice from elementi_dettagli ed, elementi e where ed.codice like '%TIF' and ed.id_elemento = e.id_elemento and e.categoria='bozzetti'; id_elemento | codice -------------+-------------- 68904 | 0M0809532TIF 67732 | Y07236TIF 67608 | 0D0731744TIF 65687 | 0M0708711TIF (4 rows) db=> update elementi_dettagliset codice = regexp_replace( upper(codice), '(IST)*TIF$', '') where id_elemento in ( select ed.id_elemento from elementi_dettagli ed, elementi e where ed.id_elemento = e.id_elemento and e.categoria = 'bozzetti' and ed.codice ~* '(IST)*TIF$' ); UPDATE 4 db=> select version(); version ---------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general