On Mon, Jan 26, 2009 at 9:45 PM, Matthias Karlsson <matthias@xxxxxxx> wrote: > On Mon, Jan 26, 2009 at 2:09 PM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: >> I wonder if this is an SQL limitation or something I'm missing in the >> PG manual, but I need to run an update on my database (to replace the >> value of a column to match a new design structure). >> >> Due to the new business logic, the replaced value of a field may end >> up being already present in the database in another record. This leads >> to unique key violations when I run the update. >> >> My question: I don't mind if the update transaction skips the records >> where the key would be violated (this preservation is in fact what we >> want) but these are only about 2% of the overall updatable records. >> >> Is there anyway to make the transaction go through with the remaining >> 98% of the update SQL which will in fact NOT violate the unique >> constraint? >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > You could always extend your update statement to include an additional > check to see if there are already rows present with the same value in > the field you are talking about. > > // Matthias > Thanks Matthias, but this seems a little recursive to me and I don't know how to do the SQL. Here is my SQL thus far. The table is "testimonials". Basically the column "user_alias" needs to be replaced to delete any mention of a user's "api_key". Both of these are fields in the same table, hence the replace logic below. As you will also see, based on our business logic, I have already kept all the related IDs in a separate small table called "testimonials_temp". This should speed up the process quite a bit because instead of going through 5 million IDs, we just loop through around 400,000. update testimonials set user_alias = replace(user_alias, '-'||api_key, '') where id in (select id from testimonials_temp) ; The problem is that after being replaced like that the "user_alias" column has a problem, because some user_aliases already exist. How should I add a check condition recursively? I tried this: update testimonials set user_alias = replace(user_alias, '-'||api_key, '') where id in (select id from testimonials_temp) and replace(user_alias, '-'||api_key, '') not in (select user_alias from links where user_alias = ?????)) ; Hope I have explained this clearly. Would appreciate any ideas! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
