Thread: How to do an UPDATE for all the fields that do NOT break a constraint?
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?
Re: How to do an UPDATE for all the fields that do NOT break a constraint?
From
Matthias Karlsson
Date:
On Mon, Jan 26, 2009 at 2:09 PM, Phoenix Kiula <phoenix.kiula@gmail.com> 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@postgresql.org) > 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
On Mon, Jan 26, 2009 at 9:45 PM, Matthias Karlsson <matthias@yacc.se> wrote: > On Mon, Jan 26, 2009 at 2:09 PM, Phoenix Kiula <phoenix.kiula@gmail.com> 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@postgresql.org) >> 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!
Re: How to do an UPDATE for all the fields that do NOT break a constraint?
From
Matthias Karlsson
Date:
On Mon, Jan 26, 2009 at 2:53 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > On Mon, Jan 26, 2009 at 9:45 PM, Matthias Karlsson <matthias@yacc.se> wrote: >> On Mon, Jan 26, 2009 at 2:09 PM, Phoenix Kiula <phoenix.kiula@gmail.com> 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@postgresql.org) >>> 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! > My idea was very similar to the SQL at the end of your post. Wouldn't something like this work? update testimonials u set u.user_alias = replace(u.user_alias, '-'||api_key, '') where u.id in (select id from testimonials_temp) and not exists (select id testimonials where user_alias = replace(u.user_alias, '-'||api_key, ''))) ? Not sure if this exact SQL is correct, but in your not in expression, you just need to make sure to refer to the user_alias of the current row being updated. // Matthias
Re: How to do an UPDATE for all the fields that do NOT break a constraint?
From
Alban Hertroys
Date:
On Jan 26, 2009, at 2:09 PM, Phoenix Kiula 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). Easiest is probably to add a new column for the new values and add a constraint to that, deprecating (or even dropping) the old column from your design. Don't forget to vacuum afterwards. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497f5540747032091416566!