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 ?

Hang on, found a reference that would indicate this syntax which also works with MySQL but not entirely sure its ANSI SQL :

INSERT INTO table (fieldA,fieldB,fieldC) VALUES("text",NULL,12345);


Would appreciate a confirmation form anyone who knows ?

no, that's not ANSI SQL

the NULL is fine (the new line \N won't work anywhere else, and i'm wondering how you got it to work in MySQL)

the doublequotes around "value" are not ANSI

Many thanks,

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.

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.

I suppose i should use single quotes ?

slowly but surely you are learning the differences between ANSI SQL and MySQL SQL

i'll get there

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....);

sorry, can't help you if i don't know your columns

if you're still on MySQL for your testing, do a SHOW CREATE TABLE

also, note that CONCAT is specific to MySQL, ANSI concatenation is accomplished with double pipe as concatenation operator

besides, concatenation two numeric ids into a string is an awkward way to target compound PK values

do it like this instead --

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 ?

now you have totally lost me

what exactly is it that you wish to distribute? the data itself?

or some kind of code? specifically, what code?

ANSI SQL for updates??? that's all?

i am lost..

The data itself.

But i want to do it by means of scripts that contain the data using ANSI SQL so the scripts can be run on any database application that supports ANSI SQL.

Forget the concatenation, asking for trouble with that, i'll do it the way you suggested. So i'll get much bigger files, can live with that.

#(#@%(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

you are embedding the data into SQL statements? sort of like steganography?

you're dead from the outset

the first step would be the CREATE TABLE statement

you will be unable to come up with one SQL statement that can run on all databases

well, you might be able to, but you'll be using a very small subset of the broad range of datatypes that various database systems support

take TINYINT, for instance... in ANSI SQL this would have to be SMALLINT because there is no TINYINT

TEXT would be very problematic, as the ANSI SQL equivalent is CHARACTER LARGE OBJECT, but not every database system supports that syntax

and so on...

distributing a CSV would seem like a far simpler strategy -- users who have databases will know how to load it, and users who don't have databases (e.g. they have excel) can also use it