You are right about your PRIMARY KEY and UNIQUE keys being able to protect most of your data. MySQL has an optional switch for INSERT, UPDATE, and ALTER TABLE statements just for some of the situations you described. It's "IGNORE" and I can vouch that it works well.
http://dev.mysql.com/doc/mysql/en/INSERT.html http://dev.mysql.com/doc/mysql/en/UPDATE.html http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html How can we detect that you have already altered a table to add a column to it? Not sure, the CREATE TABLE has an "IF NOT EXISTS" option but I don't see one for ALTER TABLE ADD column_name column_def It may be safer, in your automated scripts, to create a new table with the correct structure then copy the data over from the old table. Drop the old table, then rename the new one. Basically without the ability for your shell script to get any information from MySQL you are severely limiting your options as to how much optional execution you can perform. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Josh Howe" <[EMAIL PROTECTED]> wrote on 10/27/2004 03:52:45 PM: > > Thanks Shawn. Basically, our deployment mechanism involves applying > sql change scripts as part of the build process. I created this > simple shell script: > > host="$1" > user="$2" > pwd="$3" > > mysql -u$user -p$pwd -h$host -f sinu_com <<_EOF_ > > #Put the scripts to execute here: > \. z_worklog_alter.sql > \. z_companies_alter.sql > > quit > _EOF_ > > > I?d like to be able to run this script multiple times against the > same db without corrupting the data or structure, and without > generating error messages that don?t indicate a real problem with > one of the scripts (e.g. ?that column already exists?). To this end, > I want to put code in all of the ?.sql? files so that it only > executes once. E.g. if the sql is: > > Insert into users values (?myemail?,?mypassword?) > > I would want some thing like this: > > If(not exists(select * from users where username=?myemail?), Insert > into users values (?myemail?,?mypassword?)) > > > I think I can protect against bad data with the proper keys and > unique indexes. I?m not sure about this though. But even if I can > protect the db from corruption, I will still get a bunch of primary > key violations and such, and this will make it harder to extract the > actual errors (e.g. syntax errors in the sql) when I run the script. > > So that?s why I want to do this. I hope that made sense. I guess > that rather than trying to execute each script directly I can > execute each via an intermediate perl script that updates the db to > indicate which change scripts have been applied. That?s more complex > though. Any ideas would be greatly appreciated. Thanks! > > > > > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 27, 2004 3:30 PM > To: Josh Howe > Cc: Leo; [EMAIL PROTECTED] > Subject: RE: what is wrong woth this statement? > > > Nearly all of the T-SQL style procedural statements (IF... BEGIN... > END, WHILE...WEND, cursors, etc.) are not currently available in > MySQL. Procedural scripts , like the one you propose, will be > available in Stored Procedures (new to MySQL 5.0+). I haven't > tested that version yet, so I can't tell you if it will support a > statement like yours outside of a stored procedure (as a stand-alone > statement). > > As of right now, you still need to make control-of-flow decisions in > your programming language not your SQL statements. Sorry. > > Depending on what you are trying to do, there may be valid MySQL SQL > statement or sequence of statements that will duplicate the behavior > of the decision you are trying to make. Can you be very specific > about what action(s) you want your statement to make? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > "Josh Howe" <[EMAIL PROTECTED]> wrote on 10/27/2004 03:15:11 PM: > > > Sorry, I don't think I was very clear. I'm asking a more generic > > question about control flow expressions. I want to run a sql statement > > but only if a certain condition is met, namely if a particular record > > exists in a table. I want to do it all in a single mysql statement, like > > so: > > > > If ([record exists]) then > > Do some sql > > End If > > > > How can I do this? Thanks. > > > > > > -----Original Message----- > > From: Leo [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, October 19, 2004 11:38 PM > > To: Josh Howe > > Cc: [EMAIL PROTECTED] > > Subject: Re: what is wrong woth this statement? > > > > i didnt fully catch you... > > is this the kind of query statement you want? > > > > INSERT INTO some_other_table > > SELECT > > some_field_list > > FROM z_mail_systems > > HAVING COUNT(any_field)>0 > > > > > > On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe <[EMAIL PROTECTED]> wrote: > > > if (select count(*) from z_mail_systems > 0) then [insert statement] > > > endif; > > > > > > How do I do this kind of conditional insert? Thanks. > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
