Re: [PHP] Database Insert/Update/Delete
- Date: Fri, 9 Oct 2015 21:53:58 -0400
- From: Aziz Saleh <azizsaleh@xxxxxxxxx>
- Subject: Re: [PHP] Database Insert/Update/Delete
On Fri, Oct 9, 2015 at 9:12 PM, Stephen <stephen-d@xxxxxxxxxx> wrote:
> On 15-10-08 04:45 PM, Ashley Sheridan wrote:
>> Hi All,
>> This is a bit of an odd one, and while it's largely an SQL problem, it
>> will have PHP logic because SQL just doesn't have quite the syntax.
>> I'm sure this is a standard problem that has a standard solution, but as
>> of yet, I've not come across said solution.
>> I need to update a series of data in a DB. Some of it may exist already,
>> and some may need to be removed.
>> Now the typical for updating (but not removing) data and adding new is
>> the familiar:
>> INSERT ... ON DUPLICATE KEY UPDATE ...
>> syntax, but what about those bits which might need removing?
>> What's the typical solution that any of you use? I normally just empty
>> all rows of data sharing the common id for the set of data and then
>> insert new rows, but that seems wasteful to me, and less clean than it
>> ought to be.
> A very common solution is to not actually delete.
> Rather have a 'status' column that is set to active or deleted. Then a
> delete becomes just another form up update.
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
How is your table structured?
There are multiple solutions you can do, your business rules should dictate
how you should be doing it:
1) Remove all records and re-insert. This can be slow but if other
processes are relying on those rows, it might prove troublesome.
2) Add an updated_at field, set it for records you are adding and at the
end of the run, delete any that are relating to your ID's that are < such
3) Keep track of the ID's that have been added and have a query at the end
that removes those NOT IN.
Personally and from my experience, dealing with tables that have millions
of records, solution #2 is the fastest. There can be other solutions you
might implement that might suit your needs. Additionally, insure you have
the correct indexes set for your queries to be fast enough.