Web lists-archives.com

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.
>
>
> --
> Stephen
>
>
> --
> 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
time.
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.