sql server UPDATE与INNER JOIN结合使用

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

我在SQL SERVER中发现了以下UPDATE语句的问题

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?

请注意,INNER JOIN部分使用表本身。在这个UPDATE之后我期待一些行有更多等于-1。但我只有1分。我100%确定column1有重复项。我错过了什么?

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

我发现的问题是,有些人将列更多定义为比特类型!

1 个解决方案

#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

我这里的问题是数据类型,在位类型中你也只能有1或0或NULL。将数据类型更改为int