Quote
update test set Test_col1 = 'Test';
I am trying to input a string into an integer value. No warnings
0 row(s) affected Rows matched: 0 Changed: 0 Warnings: 0
OK, at first glance, that
does look a bit odd.
Until you realise what's actually happening ...
Quote
If I try update test set Test_col1 = 7; I get 0 row(s) returned
Symptom: You update Test_col1 to the value 7 for
every single row in the table and yet it reports zero rows updated.
Conclusion: There are
zero rows in the table!
To confirm this? What does this query return?
select count( * )
from test ;
Is this "strange" behaviour?
To you and I, perhaps. To MySQL (and most other, Relational DBMSs), absolutely not.
You and I might expect MySQL to look at the
query and say "That value can't go into that column; ERROR!".
It does not do this.
SQL works the "wrong" way round.
It
first constructs a set of all the rows it intends to work with (based on the "from", "join", "where" and other clauses) and
only then applies the changes that you specify (in the "set" clauses).
Because it find no rows
to change, it
doesn't even try to set the invalid, string value into the integer column, which is the point at which the error would get raised.
Regards, Phill W.