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.
