Overwrite data with certain properties. 2

[TamedTech]

Hello guys,

I'm recording some data on certain users into a table, but I want to keep it so as there is only one record per day that is kept for each user.

So when they action a request for user '1' for instants, it checks to see if a record for today with that user has been created, if not it creates one and records its values, if one already exists then it will update the data with the values in my current request.

Any suggestions on how to achieve this?

Thanks,

Rob

 

[TonyGroves]

If you use REPLACE instead of INSERT, then any existing duplicate-key record will be overwritten, instead of causing an error. Example:
[tt]
REPLACE tblname
SET user=1,col2='abc',col3='def'
[/tt]
or
[tt]
REPLACE tblname (user,col2,col3)
VALUES (1,'abc','def')
[/tt]

 

[audiopro]

You don't say what language the controlling program is using but I would do the checking and subsequent file update/create via a query within the control script.

Keith

 

[TamedTech]

I'm using Coldfusion to develop my application. But i was hoping to have MySQL do the work for me if possible.

Can you elaborate on that REPLACE statement? which fields does it check to see if they are identical?

Thanks,

Rob

 

[r937]

checking first and then doing the UPDATE or INSERT is twice as slow as the REPLACE method

it's even slower than just doing the INSERT and then if that errors, doing the UPDATE

r937.com | rudy.ca

 

[TonyGroves]

Any records where there is a primary-keyed or unique-indexed field or field-combination with the same value as the record being inserted will be overwritten when you do a REPLACE.

For example, if you are inserting a record whose "id" value is 123, and the "id" field is the primary key, then any existing record whose "id" is 123 will be overwritten.

 

[audiopro]

Sorry if I am being a bit numb but how can a record be replaced if it doesn't exist?
The check was to see if the app needs to create a new record or replace an existing one.

Keith

 

[r937]

audiopro, the REPLACE statement becomes an INSERT if the row doesn't exist -- check the documentation



r937.com | rudy.ca

 

[TamedTech]

Thanks for clarifying that for me guys,

One more question on the subject if I may, you have explained that the REPLACE statement works on the concept of a primary key.

I dont think this will quite meet my requirements as each record in this 'log' is given a primary key which auto-increments, I need to determine whether the record exists first dependant on a fields called 'unit_id' and then by the 'date_created' field.

So lets say someone goes to enter a record into the database, the record is for unit 1 and the date is 01/01/2006, now if this record already exists where the unit_id is 1 and the date is today then i need to update the record, if however there isn't a record for today for unit 1 then i need to create one.

I can use the serverside code to query the database a few times to look for the record, but as r937 points out this will be a little slow.

Any ideas on if/how i can use that REPLACE statement to achieve this?

Thanks,

Rob

 

[r937]

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.

create your unique index on (unit_id,date_created)

r937.com | rudy.ca

 

[TamedTech]

Ok thanks guys,

I'll give it a thrash through and let you know how i get on.

Rob

 

[audiopro]

r937
Thanks for that - always learning.

Keith

 

[TamedTech]

That worked a charm thanks guys, its not super quick but its good enough.

Thanks,

Rob