Postgres/Mysql Changing the data of a repeating field

Asked

Viewed 190 times

0

Good morning I have the "areas" table, In it I have the column "f_name"

I want to transform the "f_name" column to Unique id. However, there are repeated data currently.

I need an Sql command where I scan this column and when I find repeated change to the current data followed by character 2. Example:

Andre Machado -> alters to Andre Machado2

The Unique Id code I already have, I will apply right after the change:

ALTER TABLE areas ADD CONSTRAINT constraint_name UNIQUE (f_name);

Note. Due to the complexity, the result is also accepted by changing all those that are repeated to an alteatous number at the end. Example: "André Machado 8989", and "André Machado 7436", without making a difference between the data of the first field and the data of the second field, facilitating.

I imagine it would be a similar sql:

update table set f_name = f_name || ' ' || Rand(1111,9999) Where Count(f_name) = 2

I need for postgres, but can be in mysql!

php mysql sql postgresql

  • Êta gambiarra! It would not be better to correct the system and change the incorrect data already registered to the correct value?

  • My scenario would have worked better with sql code before applying the Unique id constrant. But I understand your suggestion, it would be an option.

  • For Postgresql you can develop a procedure in PL/pgsql where you loop: FOR target IN query LOOP statements END LOOP; and go incrementing an auxiliary variable and running a dynamic command for each update. Look at the documentation. In your example note that nothing prevents the function Rand to repeat a number in the range and, I believe, you should use the clause HAVING and not WHERE.

1 answer

1


Try this: 

update 
Table 
set 
f_name = f_name || f_id 
where f_name in
(SELECT f_name FROM table  GROUP BY f_name HAVING COUNT(f_name) > 1);

It’s not the best solution, because it takes all the records, including the first one, but it will be able to unlock the Sprint.

Browser other questions tagged php mysql sql postgresql

You are not signed in. Login or sign up in order to post.