I have a bash script that parses a text file and adds records to a
mysql database. Each row has a unique date field and time field. I
want to be sure i dont add a record that is already in there so for
each record i am about to add i need to verify that no other record
has the same date and time field values.
The text file i am parsing changes every 2 hours and contains about 2
days worth of data, each record represents a 5 minute interval. Every
2 hours the top X number of lines are discarded and a new ones added
at the bottom. Sometimes records are missed so the count of bottom
records added might not be the same as the number of top records
removed. So you can see it would be easy to screw up and add the same
record twice. (The text file isnt under my control)
Basically i need to do this: "if not exist record where date and time
are the same as what i am about to insert then go ahead and add this
new record"
How can i do that from within a bash bash script? Is there a simple
"Record exists" function?
Thanks
Cat22

On 15 mar, 05:57, Cat 22 <ca... (AT) invalid (DOT) org> wrote:
Have you considered a unique constraint on the table?


/Lennart

On Sun, 14 Mar 2010 22:20:20 -0700 (PDT), Lennart wrote:
That (if you mean a primary key on the date/time) would also allow a
completely transparent load, instead of a failure that has to be
handled, via the ON DUPLICATE phrase. (Whether the new record, which is
presumably the same values as the old but might not be, updates the
existing row or is dropped is up to the OP. I'd personally kind of
prefer the update on the grounds that it makes it easy to correct the
system if prior records loaded are found to be in error, and records
representing five minutes of time are cheap to process.)

--
The pig is nothing but a giant dish which walks while waiting to be
served.
--Grimod de La Reynière

Peter H. Coffin wrote:
A unique constraint is not necessarily a Primay Key. You can have only
one Primary Key on a table; you can have multiple unique constraints.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

In article <hnkenq$2gm$1 (AT) news (DOT) eternal-september.org>, Cat 22 <cat22 (AT) invalid (DOT) org> wrote:
Why separate fields, instead of a single DATETIME field holding both?

Create a uniqueness constraint on that table, referencing the date & time
column(s). Then use INSERT IGNORE instead of INSERT.

On 15 mar, 13:33, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
Like Jerry said, you can have multiple unique constraints on a table.
I did not propose the OP to replace the current primary key with a new
one. As the OP described it, the date and time columns, would -
possibly - be a good candidate key.

Personally I always felt a bit puzzled about why one candidate key
should be chosen primary.

/Lennart

"Cat 22" <cat22 (AT) invalid (DOT) org> wrote


Hi Cat,

check out "replace" instead of "insert":
"
REPLACE works exactly like INSERT, except that if an old row in the
table has the same value as a new row for a PRIMARY KEY or a UNIQUE
index, the old row is deleted before the new row is inserted.
"

http://dev.mysql.com/doc/refman/5.1/en/replace.html

Note that you need to have a primary key or unique index.
You probably need to create the unique index on the "date + time"
fields somehow.

R.

Doug Miller wrote:

The original text file (my data source) had separate date and time
fields. at the time i started this i didnt think about the
implications of that and simply "followed suit". Ah... hindsight eh?
Cat22

Cat 22 wrote:

Thanks for all the replies, here's what i came up with:
result=$(mysql -uxxxx -pyyyy database_name -e "SELECT COUNT(*) AS
NUMBER FROM $table WHERE Date='$adj_date' and Time='$adj_time'"
echo "$result"|grep -v NUMBER
This gives me a number of rows with that date and time, normally a 1
or a 0
adj_date and ad_time are reformatted dates and times with the date
reformatted to YYYY-MM-DD and time is reformatted from the totally
bizarre input of 6:05p 12:30a formats into the standard HH:MM format.
I am a total newbie in mysql but i am finding it fascinating and wow,
is it fast, compared to parsing stuff and adding columns with scripts
I'm combing the web for mysql reading material
Thanks
Cat22

On 16 mar, 06:21, Cat 22 <ca... (AT) invalid (DOT) org> wrote:
I've got the impression that - you believe - it is not a big problem
for you, but you should be aware that it is possible to add a row
between your query above and the actual insert. You might want to add
concepts like transaction and integrity in your quest for reading
materials.

/Lennart