Re: update a row only if any column has changed, in a very large table
Take a look here. http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html timestamp field can be autoupdated and autoinitilizated With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp. CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); And after that, just export data a defined timestamp. I think this is the easiest way and more straight forward... On Sat, Apr 6, 2013 at 9:49 PM, Rajeev Prasad rp.ne...@yahoo.com wrote: thx all, the source data is in text file. - Original Message - From: h...@tbbs.net h...@tbbs.net To: mysql list mysql@lists.mysql.com Cc: Sent: Saturday, April 6, 2013 8:02 PM Subject: Re: update a row only if any column has changed, in a very large table 2013/04/06 13:56 -0700, Rajeev Prasad I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once every day. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but changes/add and delete are not too many (may be a few hundreds.. max) Sounds like a case for replication (look it up: http://dev.mysql.com/doc/refman/5.5/en/replication.html http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is statement-replication, and row-replication. The former replicates all operations on the database, in the form wherin they were made. False changes (changing a field to its former value), too, are recorded. The latter records only those changes to a table that are real changes. (In MySQL statement-replication is of earlier implementation.) After changes are recorded, they are passed from the master --the wellspring of the changes-- to the slave --the taker of them. These are not tools, as such: replication is something implemented in the database-management system. If both your databases are in MySQL you can get help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
update a row only if any column has changed, in a very large table
hello, I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once everyday. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but chnages/add and delete are not too many (may be a few hundreds.. max) ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: update a row only if any column has changed, in a very large table
If'n it were my nickel, here is how I would solve the problem (at a somewhat high level). That is, assuming I had an ETL tool available. 1. Create landing tables for your source data. 2. Load data from the source table(s) to your new landing table(s). 3. Perform lookups from the new landing table to target to identify: inserts, updates, deletes, do nothings. 4. Write these status back to the landing table. (Want to separate the inserts from the updates from the deletions.) 5. Load the 'inserts' to your target table. 6. Load the 'updates' to your target table. 7. Perform the 'deletes' on your target table. And, one other thing that I would do is to log counts and times...so I could go back over time and evaluate performance. But then again, I work with ETL tools...so, that is my proverbial hammer. And given that, everything pretty much looks like a nail. -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Saturday, April 06, 2013 3:57 PM To: mysql list Subject: update a row only if any column has changed, in a very large table hello, I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once everyday. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but chnages/add and delete are not too many (may be a few hundreds.. max) ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update a row only if any column has changed, in a very large table
2013/04/06 13:56 -0700, Rajeev Prasad I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once every day. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but changes/add and delete are not too many (may be a few hundreds.. max) Sounds like a case for replication (look it up: http://dev.mysql.com/doc/refman/5.5/en/replication.html http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is statement-replication, and row-replication. The former replicates all operations on the database, in the form wherin they were made. False changes (changing a field to its former value), too, are recorded. The latter records only those changes to a table that are real changes. (In MySQL statement-replication is of earlier implementation.) After changes are recorded, they are passed from the master --the wellspring of the changes-- to the slave --the taker of them. These are not tools, as such: replication is something implemented in the database-management system. If both your databases are in MySQL you can get help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update a row only if any column has changed, in a very large table
thx all, the source data is in text file. - Original Message - From: h...@tbbs.net h...@tbbs.net To: mysql list mysql@lists.mysql.com Cc: Sent: Saturday, April 6, 2013 8:02 PM Subject: Re: update a row only if any column has changed, in a very large table 2013/04/06 13:56 -0700, Rajeev Prasad I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once every day. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but changes/add and delete are not too many (may be a few hundreds.. max) Sounds like a case for replication (look it up: http://dev.mysql.com/doc/refman/5.5/en/replication.html http://dev.mysql.com/doc/refman/5.5/en/replication-formats.html). There is statement-replication, and row-replication. The former replicates all operations on the database, in the form wherin they were made. False changes (changing a field to its former value), too, are recorded. The latter records only those changes to a table that are real changes. (In MySQL statement-replication is of earlier implementation.) After changes are recorded, they are passed from the master --the wellspring of the changes-- to the slave --the taker of them. These are not tools, as such: replication is something implemented in the database-management system. If both your databases are in MySQL you can get help here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UPDATE WITH row
I think this should work: Update int_contxtd_details a, int.contxtd_details b Set a.params = b.params Where B.id=25 And a.id!=25 This will work in a php script where we cannot use more than one sql at the same time or also will work y a front end manager -Original Message- From: Nuno Pereira [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 1:42 PM To: MySQL ML Subject: UPDATE WITH row Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE WITH row
Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE WITH row
Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:41:45 PM: Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira I think you want to use a variable... SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; UPDATE int_contxtd_details SET params = @NewValue; In this case, even if you overwrite the value where id=35, you will be giving back the same value it started with so no harm. Running the UPDATE statement without a WHERE clause will be faster because it won't have to check ID values for every row to make sure it's OK to update that row. Make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS. The other way I could think to write this query was with a self-join but I don't think it would be as fast as using the variable.
Re: UPDATE WITH row
[EMAIL PROTECTED] wrote: Nuno Pereira [EMAIL PROTECTED] wrote on 08/05/2005 02:41:45 PM: Following the SELECT DISTINCT topic, I have another question. I tryed to UPDATE all the tables, replacing one column in every tables with the contents of one specific row. I tried this, but the syntax is incorrect: UPDATE int_contxtd_details SET params=(SELECT params FROM int_contxtd_details WHERE id=35) WHERE id!=35; Can anyone help? -- Nuno Pereira I think you want to use a variable... SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; UPDATE int_contxtd_details SET params = @NewValue; It works. I remembered to use the variable, SET @right_params=SELECT @NewValue := params FROM int_contxtd_details WHERE id=35; but id didn't work, because the syntax was incorrect. In this case, even if you overwrite the value where id=35, you will be giving back the same value it started with so no harm. Running the UPDATE statement without a WHERE clause will be faster because it won't have to check ID values for every row to make sure it's OK to update that row. Make sense? Yes, thanks. PS. The other way I could think to write this query was with a self-join but I don't think it would be as fast as using the variable. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update a row in a table from another row within the same table
Hi listers, using mysql 4.1.7 on SuSE Linux 9.1. I have a table containing config parameters for a web based application. this table has one particular row with the main key of example. whenever a new customer joins this application, the registration application copies this example row and creates a new row in the same table with the main key identifying the new customer. this is done using php: i select the example row and insert it into the same table changing the main key. last week a new table entry gots corrupt, and i would have urgently needed a possibility to copy some columns (not all) from the example row into the new customer's row within the same table. I thought, that this is easily done using UPDATE ... SELECT ..., but after failing and searching quite a bit I found the last line in the UPDATE-syntax documentation saying: Currently, you cannot update a table and select from the same table in a subquery. Also, there is no example of UPDATE ... SELECT in the docu. Therefore, I very much assume, that the UPDATE ... SELECT does not work at all in 4.1.7. I mean, I could have achieved it doing some php programming. But this is not what SQL is considered for. does anybody of you guys know a (sequence of) SQL statement(s), which would update some columns in a row with the values retrieved from another row withing the same table? or is this just impossible with 4.1.7? or am I just too stupid? thanks very much for your attention. schlubediwup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update a row in a table from another row within the same table
Hi listers, using mysql 4.1.7 on SuSE Linux 9.1. I have a table containing config parameters for a web based application. this table has one particular row with the main key of example. whenever a new customer joins this application, the registration application copies this example row and creates a new row in the same table with the main key identifying the new customer. this is done using php: i select the example row and insert it into the same table changing the main key. last week a new table entry gots corrupt, and i would have urgently needed a possibility to copy some columns (not all) from the example row into the new customer's row within the same table. I thought, that this is easily done using UPDATE ... SELECT ..., but after failing and searching quite a bit I found the last line in the UPDATE-syntax documentation saying: Currently, you cannot update a table and select from the same table in a subquery. Also, there is no example of UPDATE ... SELECT in the docu. Therefore, I very much assume, that the UPDATE ... SELECT does not work at all in 4.1.7. I mean, I could have achieved it doing some php programming. But this is not what SQL is considered for. does anybody of you guys know a (sequence of) SQL statement(s), which would update some columns in a row with the values retrieved from another row withing the same table? or is this just impossible with 4.1.7? or am I just too stupid? thanks very much for your attention. schlubediwup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update MySQL row using URL link?
I currently update MySQL rows using phpMyAdmin. For example, dropping the following into the phpMyAdmin GUI: UPDATE mysql_db SET publish = 1 WHERE Date = 'Sunday, August 15, 2004 21:04:32' Since I get the update info in an e-mail send whenever the form is submitted, I'd like to turn querys like the above encoded into a URL - bypassing phpMyAdmin - which when clicked, will update the row. I found this article last night: Make SQL Queries over HTTP with XML with VS.NET (http://www.aspfree.com/c/a/ASP/Make-SQL-Queries-over-HTTP-with-XML-with-VSNET/ ) SELECT CustomerId, CompanyName FROM Customer http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO = The above looks very much like what I have in mind, but for MySQL. Anybody know how to do this? Many thanks in advance, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update a row from another table
Hi, is it possible to simply update a row from one table with a row from another table using reference key. Something like this example query (which is doesn't work) UPDATE orders_products SET orders_products.status=orders.orders_status WHERE orders.orders_id=orders_products.orders_id Marek - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update a row without affecting timestamp-type column
webmaster, Friday, January 25, 2002, 8:45:10 PM, you wrote: w I have a question which just may very well be ridiculous. In one table, w we have a column of type timestamp. In normal cases, we want any changes w to this row to update this timestamp (hence the nature of this datatype). w However, there is one case where we do NOT want the timestamp to update w if we make a change to some data in that row. w Is there any way to temporarily avoid updating a timestamp type? Setting it to current value, see http://www.mysql.com/doc/C/o/Column_types.html for more info about column types. w -Ian -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update a row without affecting timestamp-type column
Hello all, I have a question which just may very well be ridiculous. In one table, we have a column of type timestamp. In normal cases, we want any changes to this row to update this timestamp (hence the nature of this datatype). However, there is one case where we do NOT want the timestamp to update if we make a change to some data in that row. Is there any way to temporarily avoid updating a timestamp type? Thanks for your time. -Ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update a row without affecting timestamp-type column
At 13:45 -0500 1/25/02, [EMAIL PROTECTED] wrote: Hello all, I have a question which just may very well be ridiculous. In one table, we have a column of type timestamp. In normal cases, we want any changes to this row to update this timestamp (hence the nature of this datatype). However, there is one case where we do NOT want the timestamp to update if we make a change to some data in that row. Is there any way to temporarily avoid updating a timestamp type? Sure. Set it to its current value. UPDATE tbl_name SET ts_col = ts_col, other_col = new_value; Thanks for your time. -Ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
