Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Update column id in postgresql

    Hi again....

    What's the query to update id of records that have the same id with other records in one table?

    For example (let's name it table FRIENDS) :

    id name
    250 Jack
    251 John
    252 Ana
    253 Leyton
    250 Aster
    251 Silas
    252 Kelvin
    253 Hanna

    What's the query to update id of Aster,Silas,Kelvin, and Hanna to 254,255,256,257??
    I have thousands records that have same id in one table (it's only the id is the same), another columns/fields have different values.
    I try to use a looping, but it didn't work out.
    Here is my code :

    ---Make a function to update the id
    CREATE OR REPLACE FUNCTION update_id()
    RETURNS boolean AS
    $BODY$

    declare

    maxid integer;
    star integer;
    i integer;

    begin

    select max(id) into maxid from FRIENDS;
    select count(*) into star from FRIENDS;

    for i in maxid+1..star
    loop
    UPDATE FRIENDS SET id = maxid;
    end loop;
    return true;
    end;

    $BODY$
    LANGUAGE 'plpgsql' VOLATILE
    COST 100;

    ---To execute the function
    select * from update_id();

    Thank's for the attention and for the help...

    Ricky

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You've got a real mess here. What is going to happen to matching records in other tables? What version of pg is this?

  3. #3
    Join Date
    May 2008
    Posts
    277
    Unless there's some compelling reason to keep any of the current ids, I'd just create a new sequence and then update the table id from the sequence:

    Code:
    CREATE SEQUENCE friends_seq;
    
    UPDATE friends
    SET id = nextval('friends_seq');
    To ensure this doesn't happen again, put a unique constraint (or primary key) on the id column and use the sequence as the default value.

    As artacus points out, though, if other tables rely on this id number, then this is just the tip of the iceberg.

  4. #4
    Join Date
    Mar 2010
    Posts
    9
    Futurity, thank you for your help. Now the ID is correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •