mysql更新。计数，其中列为空并且列相同的行

Question

我想用 where 子句具有相同值的行数来更新表中的所有行。

SQL

//This gives me the rows I want to update    
SELECT file1 FROM database.TableName where file2 is null
//From there I want to count all the rows that are the same
SELECT Count(*) FROM database.TableName where file1 = resulted value
//And then I want to update the row

My current query looks like this

UPDATE database.TableName AS A 
SET refCount = (SELECT Count(*) 
FROM database.TableName AS B 
WHERE A.file1 = B.file1 AND A.file2 is null)    

TableName

|ID | file1     |   file2   | refCount |
| 1 | file.txt  |           |    1     |
| 2 | file.txt  |           |    1     |
| 3 |           | file2.txt |    1     |
| 4 | file3.txt |           |    1     |

TableName (Expected Results)

|ID | file1     |   file2   | refCount |
| 1 | file.txt  |           |    2     |
| 2 | file.txt  |           |    2     |
| 3 |           | file2.txt |    1     |
| 4 | file3.txt |           |    1     |

Answer 1

看起来您缺少更新语句的 where 子句。

试试这个...

UPDATE database.TableName AS A 
SET refCount = 
(SELECT Count(*) FROM database.TableName AS B WHERE A.file1 = B.file1 AND A.file2 is null)
where A.file1 = B.file1