> SQL does not exist in a vacuum.
> You have to run a client.
> The client is part of or runs under some scripting language ( perl ,
> php, sh, command.com )
> which in turn runs on an operating system.
> You have to somehow pass values that you want to select, update,
> or insert.
> So it really not possible to run JUST SQL.
> What are you using?
mysql < foo.sql
Our nightly report generation script is a 400 line .sql file. Our hourly
report generation script is a 280 line .sql file. No scripting language
using some DB API to feed queries to the server -- just "mysql <
whatever.sql".
What one can accomplish with this approach is quite limited by the fact that
MySQL's SQL implementation doesn't rise to the level of full programming
language unlike, for example, Oracle's PL/SQL in which it is quite possible
to write loops, conditional logic, etc.
Now, to address the original question:
If field X is a unique field (PRIMARY KEY or UNIQUE INDEX) you could try
this:
INSERT IGNORE INTO whatever SET x = '1234', ...;
UPDATE whatever SET ... WHERE x = '1234';
Under ideal circumstances this results in a bit of redundant DB access,
which may or may not matter to you depending on your circumstances.
If there is no uniqueness constraint on field x, then this technique will
not work and it's unlikely that just SQL (MySQL's SQL anyway) will be
adequate.
The TODO list for MySQL contains exactly this item though ("update a row if
it exists, otherwise insert it", aka "REPLACE INTO behavior for UPDATEs")
but no estimate as to when it will be included.
-JF
>
> Corey Wallis wrote:
>
> >Peoples,
> >
> >I'm currently trying to work out if this is possible by SQL. I
> have the need
> >to use SQL and only SQL to achieve the following.
> >
> >If a record exists and meets a certain criteria (i.e. field X =
> '1234') then
> >update the record. If the record doesn't exist then insert it.
> >
> >For reasons too complex to go into at this stage using anything
> except SQL
> >is not possible.
> >
> >Any and all suggestions welcome.
> >
> >-Corey
> >
> >
> >---------------------------------------------------------------------
> >Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
