Jon,
This is exactly what was needed. You are correct in that we're using the
mysql <foo.sql syntax for our importing. It is the only way we can get
access to the hosting companies DB server. Except of course via PHP but that
isn't suited to mass importing remotely.
Thank you for your suggestion on the INSERT IGNORE syntax. The load on the
Database isn't terribly important as the query will run infrequently. The
main concern was that the information in the table be updated if it exists
and if it doesn't it needs to be inserted. My apologies for not stating in
my original email that the column I called x was a unique primary key field.
Replace wasn't an option in this case as there are fields that can't be
updated remotely that must still contain their original data after the
update has occurred on other fields.
Many thanks for your assistance.
-Corey
----- Original Message -----
From: "Jon Frisby" <[EMAIL PROTECTED]>
To: "Gerald Clark" <[EMAIL PROTECTED]>; "Corey Wallis"
<[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, July 27, 2002 8:16 AM
Subject: RE: Fw: Complex SQL assistance
> > 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
