sql server UPDATE combined with INNER JOIN with itself

Question

I found a problem with the following UPDATE statement in my SQL SERVER

UPDATE
    table_a
SET
    table_a.More = -1
FROM
    table_a
INNER JOIN
    ( SELECT column1, COUNT(*)  AS More 
      FROM table_a 
      GROUP BY column1 ) AS table_b
ON
    table_a.column1 = table_b.column1

Note that the INNER JOIN part uses the table itself. After this UPDATE I am expecting some rows has More equals to -1. But I only got 1s. I am 100% sure that column1 has duplicates. What am I missing?

The problem I found out is that some guy defined the column More as a bit type!

Answer 1

I the problem here is the data type, in a bit type you can only have 1 or 0 or NULL also. Change the data type to int