OT] how to update 2 values in each of 3000 records in MySQL

Wed May 18 17:00:54 EDT 2011

OK, sounds like you have to update each record individually based on a
product code of some sort which I'd guess is probably the primary key.  If
that's the case, updating should be very fast.  Don't know if updating 1
record at a time would be faster than batching the updates into 10/20/30 at
a time.
Pete
Molly's Revenge <http://www.mollysrevenge.com>

On Wed, May 18, 2011 at 1:42 PM, Matthias Rebbe <runrev260805 at m-r-d.de>wrote:

> Hi,
>
> thanks so far to all for your suggestions.
>
> The database is from an online shop (xtCommerce)
> I have to update the products table which has no indexes:
>
> 1. setting a product active/inactive
> 2. setting the shipping time of a product (on stock, not on stock, ordered)
> 3. adjust the available quantity of the product.
>
> What i have so far:
>
> I retrieve a list of all "active" products with available quantity and also
> the ordered quantity (if ordered from supplieer)  from our local merchandise
> management system (MS SQL server).
> I retrieve a list of all "active" products with quantity and shipment
> status from the online store (MySQL server).
> I then compare both lists and create a list of products which have changed
> and so have  to be updated.
>
> I will try  both variants: A standalone with direct access to the db and
> also the combination standalone and .irev.
> And i will try updating record for record and also 10/20/30 in one step.
>
>
> Regards,
>
> Matthias
>
>
> Am 18.05.2011 um 18:08 schrieb Pete:
>
> > There shouldn't be any problem accessing a mysql database over a network
> if
> > the administrator has allowed you access to it.  Hard to say whether
> that's
> > "the right way".  Updating values in 3000 records shouldn't be a very
> time
> > consuming activity but as Terry said, it's hard to say without knowing a
> lot
> > more about your db structure.  Are you putting the same values into all
> 3000
> > records?  If not, how many different values are there and how do you
> > identify which records are to be updated for each value?  Probably would
> > also need to know if you have indexes set up in your database which would
> > speed up the retrieval of the records to be updated.
> > Pete
> > Molly's Revenge <http://www.mollysrevenge.com>
> >
> >
> >
> >
> > On Tue, May 17, 2011 at 11:56 PM, Terry Vogelaar <tvogelaar at de-mare.nl
> >wrote:
> >
> >> Hi Matthias,
> >>
> >> I don't think you should call this OT; sounds on topic to me.
> >>
> >> Of course it is hard to come up with a solution when we know so little
> >> about this particular database and the goal you try to accomplish with
> it.
> >> But you might reconsider the architecture of the database. If the 3000
> >> records have 3000 different values in these columns; your approach is
> fine.
> >> But when there is a lot of overlap in those values, you might be
> benefitted
> >> making optimal use of relational databases. For example, if you have 60
> >> value pairs, repeated over and over again in these columns, you replace
> the
> >> 2 columns with 1, containing an ID number for another table. In that
> other
> >> table, you put 60 records with an ID and a value pair in each record. So
> >> when you change 1 record in this table, all of the 3000 records linked
> to
> >> this one, change with it. Again, it is hard to determine if this tip
> makes
> >> any sense in your case, but it might be useful.
> >>
> >> It should be possible to access an online DB from a standalone. Never
> done
> >> it personally, though. Using irev for it can be used as well. You can
> >> exchange data between the standalone and the irev file using POST (take
> a
> >> look at the post command in the LiveCode dictionary). In other words,
> you
> >> can fake a HTML form in the standalone. So the irev file handles the
> data as
> >> if it receives a filled in web form. It then can process it and put it
> in
> >> the database.
> >>
> >> Whether you change record after record, or 50 in one step, depends on
> the
> >> values. It is certainly possible to write a massive SQL instruction. But
> it
> >> might save your sanity when you change them one by one in a repeat loop,
> >> with a much simpler SQL query. However, if those 50 records can be
> filtered
> >> easily, and if they share values, combining might make sense. Hard to
> say.
> >>
> >> Terry
> >>
> >> Op 18 mei 2011, om 07:36 heeft use-livecode-request at lists.runrev.comhet
> >> volgende geschreven:
> >>
> >>> Hi,
> >>>
> >>> I have to update 3000 records in a mysql db very often. I just have to
> >> change only 2 values in each of the 3000 records.
> >>> I have a desktop app, which creates the values which then have to be
> put
> >> in that mysql database. This db is not local, but resides on an on-rev
> >> server.
> >>> I could activate remote access for the mysql db and could limit this
> >> access to our static ip address which our isp assigne to us to let the
> >> standalone
> >>> app to update the values directly. But is that the right way?  Should i
> >> do it directly from my standalone or should i do it through an irev
> script?
> >>>
> >>> If 2nd, how would i parse the values to the script. I know how to
> connect
> >> from an irev script to a mysql db. That would be no problem.
> >>> But i never sent data from a standalone to an irev script.
> >>>
> >>> Should i update record after record or can i update lets say 50 records
> >> in one step? Even with different values?
> >>>
> >>> Could someone help?
> >>>
> >>>
> >>> Regards,
> >>>
> >>> Matthias
> >>
> >> _______________________________________________
> >> use-livecode mailing list
> >> use-livecode at lists.runrev.com
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>
> >>
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>