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]
>
