"this Operation Will Result In X Rows Being Updated" Warning

[UCSF School of Pharmacy]

Hi, I'm using SQLyog Enterprise 5.22 and connecting with HTTP Tunneling using https.

I can successfully connect and view data and update data. However, now, for some reason unknown to me, I am unable to update data for a single row.

For example, when I do a query such as:

select * from student where namelast='smithe';

and I receive only one result and I change the picklist from “(Read only)” to “student” and I modify one field, when I attempt to save the data I receive the following warning:

“This operation will result in 120 rows being updated. Do you want to continue? (Yes) (No) (Help)”

Clicking Help opens the SQLyog online help to the Getting Started page, which does not help.

The strange thing is that I was able to update data for only one row in the past, and I do not know that anything has changed.

I can manually enter an UPDATE in the SQL window and it updates the single row (field) as I expect, but I don't understand why I can't do this through the GUI like I have in the past.

Does anyone know how I might resolve this problem?

Thanks!

[peterlaursen]

Please read:

http://webyog.com/faq/28_70_en.html

I think the reason is that you use TEXT (or BLO:cool: variables to store your strings ?

It is a common misunderstanding by 'fresh' MySQL users to use TEXTs.

Maybe because programs like MS-access use the type name 'text' for string data.

You should preferably use (var)char for storing strings unless you really need the full size of a TEXT/MEDIUMTEXT/LONGTEXT. 

MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/str…e-overview.html

[UCSF School of Pharmacy]

Peter,

Thank you for your prompt reply. Yes, I was using TINYTEXT. After altering the table so that they are VARCHAR it now works as I expect (but I still don't understand why it appeared to work for me before even when those columns were TINYTEXT). The book from which I learned MySQL compared CHAR versus VARCHAR and it also compared BLOB versus TEXT but it did not compare the TEXT data types to either CHAR or VARCHAR, so I think perhaps that is why I did not fully understand the limitations of using them or when to choose TEXT data types.

Thanks again for writing that great FAQ entry and for helping me out so quickly.

Frank

🙂

[peterlaursen]

basically SQLyog (as any MySQL client) will have to send an SQL statement like

UPDATE {what to update} WHERE {where-clause}.

1) If there is a Primary key this one will be use in the where-clause

2) If there is no Primary key the where-clause will be like 

WHERE col_1 = 'value-1' and col_2 = 'value_2' … and col_n = value_n.

However BLOB/TEXT types are generally omitted in the WHERE because they can hold very long data.

You could also have added a Primary Key to the table.

[Author]

Posts