Update-Bug in Column

[expired user #8218]'s profile image [expired user #8218] posted 10 years ago in General Permalink
Hello,

if i have a table with some columns and a lot of rows and make a change in one row of type number (integer or decimal). i type a number like 2 and additional a # instead ENTER (in the near of key #), because of thick fingers, accept the change and all rows are updadet(!!!!) not only the one row i changed!

Is that right?

In my case i had a big problem after that; there was a flag in database to mark different states for entries... you can imagine ....

peter
ansgar's profile image ansgar posted 10 years ago Permalink
The only possible situation where that happens is when your table does not contain any primary or unique key. Please post your CREATE TABLE code here.
[expired user #8218]'s profile image [expired user #8218] posted 10 years ago Permalink
I have a primary key about four columns:

CREATE TABLE `versand_leitstand` (
`AE_NUMR` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Ab-Nummer' COLLATE 'latin1_german1_ci',
`AE_STAT` VARCHAR(1) NULL DEFAULT NULL COMMENT 'Auftragsstatus' COLLATE 'latin1_german1_ci',
`AE_GLIE` VARCHAR(1) NULL DEFAULT NULL COMMENT 'Gesamtlieferung Auftrag' COLLATE 'latin1_german1_ci',
`AE_VLIE` VARCHAR(1) NULL DEFAULT NULL COMMENT 'Vorlieferung Auftrag' COLLATE 'latin1_german1_ci',
`AE_MARB` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_KDNR` VARCHAR(100) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_KDNA` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_VSNA` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_VSAD` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_VSTR` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_LANG` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_LAND` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AA_SASE` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`AE_LIEF` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Liefertermin, bei GL aus AE, sonst aus A3' COLLATE 'latin1_german1_ci',
`AE_FIXT` VARCHAR(1) NULL DEFAULT NULL COMMENT 'Fixtermin, bei GL aus AE, sonst aus A3' COLLATE 'latin1_german1_ci',
`AE_LBED` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`A3_NUMR` VARCHAR(3) NOT NULL DEFAULT '' COMMENT 'Nummer der Position' COLLATE 'latin1_german1_ci',
`A3_IDEN` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`A3_ABRF` VARCHAR(2) NULL DEFAULT NULL COMMENT 'Abruf' COLLATE 'latin1_german1_ci',
`A3_MENG` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Menge' COLLATE 'latin1_german1_ci',
`A3_GEWI` DECIMAL(10,4) NULL DEFAULT '0.0000' COMMENT 'Gewicht',
`A3_EURO` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Wert',
`A3_OFMG` VARCHAR(10) NULL DEFAULT NULL COMMENT 'offene Menge' COLLATE 'latin1_german1_ci',
`A3_VLIE` VARCHAR(1) NULL DEFAULT NULL COMMENT 'Vorlieferung Position' COLLATE 'latin1_german1_ci',
`A3_WAGR` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Warengruppe' COLLATE 'latin1_german1_ci',
`AH_NUMR` VARCHAR(255) NOT NULL COMMENT 'Lieferschein-Nummer' COLLATE 'latin1_german1_ci',
`AH_STAT` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Lieferscheinstatus' COLLATE 'latin1_german1_ci',
`A6_NUMR` VARCHAR(3) NOT NULL COMMENT 'Nummer der Lieferscheinposition' COLLATE 'latin1_german1_ci',
`A6_STAT` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Lieferscheinstatus' COLLATE 'latin1_german1_ci',
`A6_IDEN` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`A6_MENG` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Menge Lieferscheinposition' COLLATE 'latin1_german1_ci',
`A6_GEWI` DECIMAL(10,4) NULL DEFAULT '0.0000' COMMENT 'Gewicht Lieferscheinposition',
`A3_PROD` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Produktnummer' COLLATE 'latin1_german1_ci',
`AB_ZTNR` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Zolltariefnummer' COLLATE 'latin1_german1_ci',
`A3_LAEN` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Laenge',
`A3_BREI` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Breite',
`A3_HOEH` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Hoehe',
`A3_ADRM` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Aussendurchmesser',
`A3_IDRM` DECIMAL(10,2) NULL DEFAULT '0.00' COMMENT 'Innendurchmesser',
`A3_FORM` VARCHAR(5) NULL DEFAULT NULL COMMENT 'bei ZS aus der Zeichnungsnummer A3, bei HZ aus Form AB, sonst keine' COLLATE 'latin1_german1_ci',
`A3_MATE` VARCHAR(255) NULL DEFAULT NULL COMMENT 'bei HZ und ZS aus A3, KT aus AB' COLLATE 'latin1_german1_ci',
`A3_TXT1` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`A3_TXT2` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`A3_TXT3` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`FORM_ID` INT(1) NULL DEFAULT '0',
`BEME` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Bemerkung nur fuer den LV' COLLATE 'latin1_german1_ci',
`aktiv` TINYINT(3) NULL DEFAULT '0',
`regel` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_german1_ci',
`regelhinweis_lv` LONGTEXT NULL COLLATE 'latin1_german1_ci',
`regelhinweis_ps` LONGTEXT NULL COLLATE 'latin1_german1_ci',
`lieferung` INT(11) NULL DEFAULT '-1',
`sortierung` INT(11) NULL DEFAULT '0',
`cdate` DATETIME NULL DEFAULT NULL,
`mdate` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`testing` DECIMAL(10,2) UNSIGNED NULL DEFAULT '0.00',
PRIMARY KEY (`AE_NUMR`, `A3_NUMR`, `AH_NUMR`, `A6_NUMR`)
)
COLLATE='latin1_german1_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;
ansgar's profile image ansgar posted 10 years ago Permalink
I have created your table here and 3 rows with AE_NUMR=2323, 4444 and 6666. Then I updated all of the 12 integer/float columns with some value. But none of these updates touched more than one row. So, you must own some rows with special values in the primary key fields (`AE_NUMR`, `A3_NUMR`, `AH_NUMR`, `A6_NUMR`). Please post values of these fields for which you get the above described behaviour.

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