How to edit text fields

[expired user #8645]'s profile image [expired user #8645] posted 9 years ago in General Permalink
How can we edit sqlserver text fields data by using HeidiSql tool..?

I'm getting the following errors when I was trying to edit text field data with HeidiSql 9.1.

Error:-
/* large SQL query (105.2 KiB), snipped at 2,000 characters */
* SQL Error (402): The data types text and varchar are incompatible in the equal to operator. */
[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink

How can we edit sqlserver text fields data by using HeidiSql tool..?

I'm getting the following errors when I was trying to edit text field data with HeidiSql 9.1.

Error:-
/* large SQL query (105.2 KiB), snipped at 2,000 characters */
* SQL Error (402): The data types text and text are incompatible in the equal to operator. */

[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink
It seems, The tool building query as below.

update <table> set <textfield column name>=<updated data> where <textfield column name>=<old data> and other fields.

Is there way to exclude the test fields in the where clause...? Thanks.
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, HeidiSQL uses primary key columns only for the WHERE clause - when there is a primary key. If there is no pk, HeidiSQL uses all columns.
[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink
Thanks for your reply.

Here, the problem is text field value comparison in the where clause (where <textfield column name>=<old data>).It's incorrect. Right..?

I don't have primary keys in my table for some purpose.
Could you please give me a suggestions to update text fields data without errors (by providing where clause conditions by the user).

I appreciate your help. Thanks again!
[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink
Do we have any provision to apply filters while updating the record..?
ansgar's profile image ansgar posted 9 years ago Permalink
No, applying a filter to the data grid view does not modify UPDATE/INSERT/DELETE statements. You will definitely need a primary key so HeidiSQL can safely use that for updates.
[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink
Thanks Ansgar!!

Some other tools (Ex: DBVisualizer) also needed primary key. But , If there is no primary key in the table , the tool prompts the user to specify the where clause.

Can we expect this kind feature in our future releases ..?
ansgar's profile image ansgar posted 9 years ago Permalink
You will probably have noticed that HeidiSQL already supports updating without a primary key. That's the reason why it generates the error "data types text and text are incompatible in the equal to operator".

I guess this is more a server side problem, as HeidiSQL does its best to generate a valid WHERE clause. If that fails due to the above error message, you should create a primary key on that table. I don't know what HeidiSQL could do to prevent that.
[expired user #8645]'s profile image [expired user #8645] posted 9 years ago Permalink
Yes. It's updating without primary key. But, if the table has text datatype column it's failing to update because of "data types text and text are incompatible in the equal to operator".

I feel,If the tool can give the provision to the user to provide where clause while updating that would be great. The user could decide whether provide the where clause or use the default where clause which is provided by the tool. It's a great tool.

Please login to leave a reply, or register at first.