Please, I have database with following tables

CREATE TABLE `licences` (
`Id` int(11) NOT NULL auto_increment,
`Licence` varchar(30) NOT NULL,
`ValidFrom` date default NULL,
`ValidTo` date default NULL,
`Name` varchar(50) default NULL,
`State` char(1) default NULL,
`Registered` tinyint(1) default '0',
`Update` tinyint(1) default '0',
`LastConnectionDate` date default NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `Licence` (`Licence`),
KEY `Name` (`Name`)
) ENGINE=MyISAM AUTO_INCREMENT=1919 DEFAULT CHARSET=utf8;

CREATE TABLE `statistics` (
`Id` int(11) NOT NULL,
`Year` smallint(2) unsigned NOT NULL default '0',
`Month` tinyint(1) unsigned NOT NULL default '0',
`Day` tinyint(1) unsigned NOT NULL default '0',
`Flags` tinyint(1) NOT NULL default '0',
UNIQUE KEY `Id` (`Id`,`Year`,`Month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What I need is to load data from file into table Licences so that new
records are inserted and existing rows are updated without changing their ID
so that data from table Statistics uing foreign key are not deleted. I do
not know how to do that.

LOAD DATA INFILE ''C:/licences.txt'' [REPLACE | IGNORE] INTO TABLE licences
FIELDS TERMINATED BY ''\t'' LINES TERMINATED BY ''\r\n'' (Licence, Name,
ValidFrom, ValidTo, State, Registered, Update);

If I use REPLACE it inserts new rows but rows with the same key Licence are
replaced and gets new id which results in deleting data in table Statistics.

If I use IGNORE it does not update existing rows.

If I use neither REPLACE nor IGNORE it fails on first row with the same key
Licence.

Please, can you give an advice? Thank you! Vojta

On Thu, 9 Nov 2006 13:09:59 +0100, Vojta wrote:
> What I need is to load data from file into table Licences so that new
> records are inserted and existing rows are updated without changing their ID
> so that data from table Statistics uing foreign key are not deleted. I do
> not know how to do that.
>
> LOAD DATA INFILE ''C:/licences.txt'' [REPLACE | IGNORE] INTO TABLE licences
> FIELDS TERMINATED BY ''\t'' LINES TERMINATED BY ''\r\n'' (Licence, Name,
> ValidFrom, ValidTo, State, Registered, Update);
>
> If I use REPLACE it inserts new rows but rows with the same key Licence are
> replaced and gets new id which results in deleting data in table Statistics.
>
> If I use IGNORE it does not update existing rows.
>
> If I use neither REPLACE nor IGNORE it fails on first row with the same key
> Licence.
>
> Please, can you give an advice? Thank you! Vojta

Why do you HAVE TO use LOAD DATA for this, instead of a small loader
application written to handle your structure?

> CREATE TABLE `licences` (
> `Id` int(11) NOT NULL auto_increment,
> `Licence` varchar(30) NOT NULL,
> `ValidFrom` date default NULL,
> `ValidTo` date default NULL,
> `Name` varchar(50) default NULL,
> `State` char(1) default NULL,
> `Registered` tinyint(1) default '0',
> `Update` tinyint(1) default '0',
> `LastConnectionDate` date default NULL,
> PRIMARY KEY (`Id`),
> UNIQUE KEY `Licence` (`Licence`),
> KEY `Name` (`Name`)
> ) ENGINE=MyISAM AUTO_INCREMENT=1919 DEFAULT CHARSET=utf8;
>
> CREATE TABLE `statistics` (
> `Id` int(11) NOT NULL,
> `Year` smallint(2) unsigned NOT NULL default '0',
> `Month` tinyint(1) unsigned NOT NULL default '0',
> `Day` tinyint(1) unsigned NOT NULL default '0',
> `Flags` tinyint(1) NOT NULL default '0',
> UNIQUE KEY `Id` (`Id`,`Year`,`Month`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>

You can PROBABLY make LOAD DATA work if you change the statistics table
to tie to the license value instead of ID. That's probably what you
really want to do anyway, from a design perspective.

--
25. No matter how well it would perform, I will never construct any sort of
machinery which is completely indestructible except for one small and
virtually inaccessible vulnerable spot.
--Peter Anspach's list of things to do as an Evil Overlord