Unanswered: how do i insert a null value from a script ?
I have a database in MySQL that i want to make available for others.
This includes people who use other databases than MySQL.
For this purpose i create script files that are supposed to be ansi sql and therefore usable on many database systems.
In MySQL i can insert a NULL value like this:
INSERT INTO TABLE fieldX VALUES(\N);
INSERT INTO TABLE (fieldA,fieldB,fieldC) VALUES("text",\N,12345);
where the \N results in a null value for the field.
But is this ANSI SQL ?
will this work on (as many as possible) other database systems ?
in MySQL when you do a SELECT INTO OUTFILE the null values become \N i think that's where i picked it up. Works when reading files back in as well as from scripts.
the doublequotes around "value" are not ANSI
Really, then how do i differentiate between string and numerical ?
INSERT INTO table (fieldA,fieldB) VALUES("123",123);
where fieldA is actually a varchar field and fieldB is an integer,
do i simply omit the double quotes and rely on the definition of the table to be correct so the first 123 gets interpreted as string because the field is defined as string, and of course the second as integer because that field is defined as integer.
delete from racerunnerUK where concat(race_id,',',horse_id) IN ("84931,100401","84931,97256","84931,96156","84931 ,14021");
insert into racerunnerUK (race_id,horse_id,jockey_id,trainer_id,........) values ("84931","100401","1139","940",........);
I'm guessing this won't work either.
The table has a primary key consisting of the combination of race_id and horse_id so as ansi sql does not know the REPLACE comand what i do first is delete then re-insert.
The script is intended as an update to an existing table so there may be records that are not re-inserted again and i can only get rid of those by deleting them first then re-inserting those records that do still exist.
Simply use single quotes ?
delete from racerunnerUK where concat(race_id,',',horse_id) IN ('184931,100401','84931,97256','84931,96156','8493 1,14021');
insert into racerunnerUK (race_id,horse_id,jockey_id,trainer_id,....etc.... ) values (84931,100401,1139,NULL,....etc....);
So the ANSI SQL equivalent would be:
delete from racerunnerUK where (race_id || ',' || horse_id) IN ('84931,100401','84931,97256','84931,96156','84931 ,14021');
I know the concatenation is awkward and i'm just doing that to keep the filesize of the scripts down. This table has over 1 million records. The biggest script contains data on 1 year and that can exceed 150000 records. So even though the syntax is awkward it does result in a lot smaller files. If it doesn't work as ANSI SQL then i can always swich to the syntax like you suggest.
There are other tables as well but this one is the only one with a combined primary key.
CREATE TABLE `sbs_racingdb`.`racerunneruk` (
`HORSE_ID` mediumint(1) unsigned NOT NULL,
`RACE_ID` mediumint(1) unsigned NOT NULL COMMENT 'Unique ID for a race specific to SBS',
`runnerstatus` varchar(25) default NULL COMMENT 'Entry, Non-runner, Runner',
`Entry` tinyint(3) unsigned default NULL,
`Draw` tinyint(3) unsigned default NULL,
`Position` tinyint(3) unsigned default NULL,
`tempDistance` varchar(255) default NULL,
`AGE` tinyint(2) unsigned default NULL COMMENT 'Age of horse at time of race, could be replaced when horses date of birth is known.',
`STONE_WEIGHT` tinyint(2) unsigned default NULL COMMENT 'First part of weight, STONE.',
`POUND_WEIGHT` tinyint(2) unsigned default NULL COMMENT 'Second part of weight, POUNDS.',
`TEMP_HEADGEAR` varchar(10) default NULL COMMENT 'To be examined at a later stage.',
`RATING` tinyint(2) unsigned default NULL COMMENT 'Official rating. Max.nr. found so far 148',
`SP1` smallint(6) default NULL COMMENT 'First part of SP price.',
`SP2` smallint(6) default NULL COMMENT 'Second part of SP price.',
`FC1` smallint(6) unsigned default NULL,
`FC2` smallint(6) unsigned default NULL,
`REPORT` text COMMENT 'Report on horses performance. longest report found 833 characters',
`TEMP_BETTING` varchar(20) default NULL COMMENT 'Early prices published by Sportinglife, to be examined at a later stage.',
`JOCKEY_ID` mediumint(1) unsigned default NULL,
`JOCKEYALLOWANCE` tinyint(2) unsigned default NULL COMMENT 'Weight in pounds(?)',
`TRAINER_ID` mediumint(1) unsigned default NULL,
`TEMP_SP_REMARK` varchar(2) default NULL COMMENT 'Indicated if horse was favourite, to be examined at a later stage.',
`TEMP_LTOCDWBF` varchar(255) default NULL COMMENT 'Notes for horse, to be examined at a later stage.',
`TEMP_FORM` varchar(10) default NULL COMMENT 'Result for horse in last races, to be replaced by own query.',
PRIMARY KEY (`RACE_ID`,`HORSE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Horses participating in a race.';
Thats not it is it, i'm searching the net but just getting more confused.
Please point me in the direction of a location where ANSI SQL is clearly explained with examples.
Or let me rephrase my question.
Got a MySQL database and i want to create ANSI SQL script files that function as update files. One script file per table seems appropriate. The reason for ANSI SQL is that i want the database to be available to non-MySQL users as well. Obviously providing the used database application can handle ANSI SQL script files. If not i provide basic .CSV file.
What is the syntax in ANSI SQL to delete then insert records ?
#(#@%(U@Q#U)U(T#)%(& this is embarrassing, i really put my foot in it this time.
If a record gets deleted on the master database then i can't delete it using a script from the local database because i simply don't know it's in there. And i can just delete everything as then i'll delete records that should remain. How did i miss this
Thanks for the help Rudy, i'm going back to the drawing board first.
At least you've made me see the error of my ways