MySQL Forums
Forum List  »  MySQL Workbench

Re: Workbench update query unresponsive
Posted by: Phillip Ward
Date: August 19, 2022 05:28AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Workbench update query unresponsive
49
August 19, 2022 05:28AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.