On Fri, 2015-10-09 at 21:53 -0400, Aziz Saleh wrote: > 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. Hi Aziz, Thanks for that. I was thinking along similar lines myself, I just wondered if there was some better way that I was completely missing. The timestamp method would work well for me for keeping older versions of the content, which is something I plan on doing with this project later on down the line. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
