Mark duplicate fields in a table

Question

Mark duplicate fields in a table

Navigation

#1 by (6 votes)

3

I have a table in my database and would like to mark occurrences where certain code repeats itself. Remember that this code field is not a primary key.

Example:

Data        Código    Repetição   Status    ...
25/2/2014   138863    ok          C96       ...
25/2/2014   387707    ok          C96       ...
25/2/2014   387707    nok         C96       ...
25/2/2014   387707    nok         C96       ...
25/2/2014   387707    nok         C96       ...
25/2/2014   1845141   ok          C96       ...
25/2/2014   1845141   nok         C96       ...
25/2/2014   1845141   nok         C96       ...
25/2/2014   1845141   nok         C96       ...
25/2/2014   1845141   nok         C96       ...
25/2/2014   1845141   nok         C96       ...

Does anyone have any idea how I can create the repeating column with the above logic (first occurrence, mark as ok , in other nok ) using some query?

I am currently using this table in an Access database.

mysql sql ms-access

asked by anonymous 12.03.2014 / 21:07

1 answer

How to give automatic permissions in shell script Error date_diff PHP

score 6 · Accepted Answer

It is possible with a subquery that returns one element per code.

Let's look at an example with the structure below:

create table teste (
    id int primary key auto_increment,
    codigo int,
    repeticao  varchar(10)
);

The following query returns a ok only once per code:

select
  codigo,
  case
    when id = (
      select min(t2.id)
      from teste t2
      where t2.codigo = teste.codigo
    )
    then 'ok'
    else 'nok'
  end repeticao
from teste

View sqlfiddle

As an update, you can do this:

UPDATE teste
SET repeticao = 
    case
        when id = (
            select min(t2.id)
            from (select * from teste) t2
            where t2.codigo = teste.codigo
        )
        then 'ok'
        else 'nok'
    end;

Note that MySQL does not allow you to select the table itself in a subselect in the UPDATE command, but the (select * from teste) snippet is a workaround to circumvent this limitation.