How to do an UPDATE for all the fields that do NOT break a constraint?

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!

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

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!