Updating keywords

I'm in the process of revising our keywords. For example,
changing
'Oceans and Coasts' to just 'Oceans'. Since 'Oceans' already
exists as a
keyword, I can't simply edit 'Oceans and Coasts' and switch
it to
'Oceans' (from the UI).

Thus, I used psql:

update story_keyword set keyword_id = newID where keyword =
oldID;

This worked fine. I then needed to change 'Coral Reefs' to
'Oceans', but
got the following error message since some of the stories
tagged with
'Coral Reefs' are already tagged with 'Oceans':

ERROR:  duplicate key violates unique constraint
"pk_story_keyword"

Any ideas on how to get around this?

Chris

On Feb 27, 2007, at 17:06, Chris Schults wrote:

> This worked fine. I then needed to change 'Coral Reefs'
to  
> 'Oceans', but
> got the following error message since some of the
stories tagged with
> 'Coral Reefs' are already tagged with 'Oceans':
>
> ERROR:  duplicate key violates unique constraint
"pk_story_keyword"
>
> Any ideas on how to get around this?

First delete the dupes:

   DELETE FROM story_keyword
    WHERE keyword_id = $old_id
      AND story_instance__id IN (
          SELECT story_instance__id
            FROM story_keyword
           WHERE keyword_id = $new_id
    );

Then do the update:

   UPDATE story_keyword SET keyword_id = $new_id where
keyword =  
$old_id;

Best,

David