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

You've got a real mess here. What is going to happen to matching records in other tables? What version of pg is this?

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:

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.

Futurity, thank you for your help. Now the ID is correct.