distal-attribute
distal-attribute
distal-attribute
distal-attribute

can not update col2, but col1 no problem

User, date Message
Written by laode
3 years ago
Category: Running SQL scripts
3 posts since Wed, 20 Apr 11
Hi,

I checked a lot of corners, but I can't find the reason for not updating col2, while update of col1 works fine.
Also tested: creating table 'dummy' with statements as generated by Heidi -> update is fine !???

Here impressions of what I did and tested:

original table as imported in mySQL from flat-file
and generated by Heidi:

CREATE TABLE `t_cc_vr` (
...
`BETRAG` DOUBLE(10,2) NULL DEFAULT NULL,
`STAND` DOUBLE(10,2) NULL DEFAULT NULL,
...
)
COLLATE='latin1_german2_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

UPDATE T_CC_VR SET BETRAG=123456.12 WHERE K_ID=3 LIMIT 1;
/* 1 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

UPDATE T_CC_VR SET STAND = 111 WHERE K_ID = 3 ;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

refreshing the table-outpüut shows indeed the old value for STAND, while BETRAG was updated successfuly !

There is no warning, no error-message, just update is ignored for column STAND !

I created a dummy-table by geration from Heidi:

CREATE TABLE dummy (
`BETRAG` DOUBLE(10,2) NULL DEFAULT NULL,
`STAND` DOUBLE(10,2) NULL DEFAULT NULL
);

Insert and update happen successful without errors !?

What can I test more ?
How and where to search for not-updating-reasons ?

Thanks for any tip,

Laode
Written by ansgar
3 years ago
4793 posts since Fri, 07 Apr 06
Check what SHOW WARNINGS says, please.
Written by laode
3 years ago
3 posts since Wed, 20 Apr 11
Hi,

Thanks for taking care for this case. I hope I can provide all Heidi-feedback as you need.
There is no pop-up message or anything like error-message.

Here is the feedback as of bottom-window:

UPDATE T_CC_VR SET STAND = 1958.02 WHERE K_ID = 3 ;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,016 sec. */
show warnings;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

my next tests will be:

1. create identical table with other name
2. run "insert as select..." to copy all columns, all records into new table
3. test update in new table

if that does not solve, I will drop this DB and rebuild complete new, running the same loading.

This will cost some days. I will post result here.

LaoDe
Written by ansgar
3 years ago
4793 posts since Fri, 07 Apr 06
You should not put so much effort in unrelated stuff. Could also be your row with k_id=3 already had the value you passed in the UPDATE, in which case the server does not affect any row.
Written by laode
3 years ago
3 posts since Wed, 20 Apr 11
Hi Anse,

Avoiding effort is a nice target, but how to figure out about a new tool, what it can and if not, which particular functionality might crete the problem ?

So far, I copied the table and here is a compare:

update t_cc_vr_copy set STAND = 555.02 where K_ID = 3;
/* 1 rows affected, 0 rows found. Duration for 1 query: 0,015 sec. */
update t_cc_vr set STAND = 555.02 where K_ID = 3;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,000 sec. */

so I see clearly now, that original table is blocking somehow for unknown reason any update on column STAND. And from the calues it is also very clear now, that this value wasn't in that field before.

Test 2 will be drop table -> create table -> load from flat-file.

I let you know about the result.

cheers Laode
Written by kalvaro
3 years ago
563 posts since Thu, 29 Nov 07
I'm not fully sure of what the problem is but here's my advice:

1. Before doing the UPDATE, run a SELECT query with the same WHERE clause. That's the only way to make sure there are matching rows.

2. Take note of the old values. MySQL won't update a matching row if the new values are the same as the old ones.

3. Run the SELECT query again after the UPDATE to check if the final row values are the expected ones.
 

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