MySQL Forums
Forum List  »  MyISAM

Deletion Woes
Posted by: Randy Smith
Date: January 15, 2020 11:18AM

I have a very large table with many fields, among them a "post_date" field established as varchar(50) DEFAULT NULL and "salary" set up as decimal(11,2).

From a LOAD INI, there are some post_date entries with invalid date fields (e.g. one has "201", for instance) and some salary fields that do not have decimals (e.g. "0" instead of "0.0".)

I can find these by select, but when I change the select to a delete, I'll get "incorrect datetime value" or "incorrect decimal value". I tried "delete from table x where id in (select id from table x where year(post_date) is null)", a select that works fine on its own, but from that I get "you can't specify target table x for update in from clause.".

I tried to set salary to 0.0 or NULL with the same "incorrect decimal value: '0' for column '' at row -1" error.

How can I get rid of these bad rows of data?

Options: ReplyQuote

Written By
Deletion Woes
January 15, 2020 11:18AM

Sorry, you can't reply to this topic. It has been closed.

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.