Mark duplicate fields in a table

Question

I have a table in my database and I would like to mark the occurrences where a certain code is repeated. Remembering 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 repeat column with the above logic (first occurrence, mark as ok, in the other nok) using some query?

At the moment I am using this table in a Access Database.

Answer 1

It is possible with a subquery that returns an element by code.

Let’s take 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

See the sqlfiddle

---

In update form, you can do so:

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 selecting the table itself in a subselect in the command UPDATE, but the stretch (select * from teste) is a workaround to circumvent this limitation.

See the sqlfiddle