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

[Matthias Rebbe]

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.com het
>> 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