CODEWITHSUNDEEP
mysqlsqlselectsubquery
timh
timh

Reputation: 1590

Why is this Update/Select subquery wrong?

Ok so I run this query to get a count, and this is correct:

mysql> select count(medias.fileRef) from medias where fileRef=20193621;
+-----------------------+
| count(medias.fileRef) |
+-----------------------+
| 135869 |
+-----------------------+

So then I run this to place that count into another table:

mysql> update files set refCount=(select count(medias.fileRef) 
       from medias where fileRef=20193621) where id=20193621;

Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 1

(note that medias.fileRef is an INT which contains files.id), and files.refCount should contain a total count of all medias rows pointing at that files.id:

Then I go check it, and its wrong.

mysql> select refcount from files where id=20193621;
+----------+
| refcount |
+----------+
| 127 |
+----------+

How can this possibly be? What am I doing wrong here?

Upvotes: 0

Views: 139

Answers (1)

Wasif
Wasif

Reputation: 522

This is because your files.refcount column data type is TINYINT. Change it to INT or any other reasonable numeric type.

Upvotes: 4

Related Questions