09-30-2009
Hi.
Sorry, I'm not so familiar with mySQL, but as you didn't get an answer yet!
This is based on Oracle, but the same ideas should relate.
Firstly it's safe to update a table with however many rows. The worst that could happen is that the update fails and the uncommitted changes are rolled back.
You could:
- split the updates in to smaller batches
- add commit statements every few hundred / thousand statements
- instruct your sql client to commit every few hundred / thousand statements
- add more redo / undo logs or make your existing ones larger, or both
- switch off logging on the table (alter table .. nologging if mySQL supports that) to reduce redo / undo (in most cases)
- disable referential / unique constraint indexes (more for performance)
Last edited by Scott; 09-30-2009 at 07:48 PM..
10 More Discussions You Might Find Interesting
1. Web Development
Hello.
I'm not sure how I can get around this, or what I am doing wrong, but I need some help. :)
I want to do an select query looking like this:
SELECT venue, SUM( amount )
FROM IWD
WHERE venue = 'Foxy Hollow'
Unfortunately I need to have spaces in the names in these fields,
is... (10 Replies)
Discussion started by: noratx
10 Replies
2. Shell Programming and Scripting
Dear All,
I have a data file input.csv like below. (Only five column shown here for example.)
Data1,StepNo,Data2,Data3,Data4
2,1,3,4,5
3,1,5,6,7
3,2,4,5,6
5,3,5,5,6
From this I want the below output
Data1,StepNo,Data2,Data3,Data4
2,1,3,4,5
3,1,5,6,7
where the second column... (4 Replies)
Discussion started by: ks_reddy
4 Replies
3. UNIX for Dummies Questions & Answers
I have 2 files,
file01= 7 columns, row unknown (but few)
file02= 7 columns, row unknown (but many)
now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there
e.g.
file 01
James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies
4. UNIX for Dummies Questions & Answers
Hi,
I have a tab delimited text file where the first two columns equal numbers. I want to delete all rows where the value in the first column equals the second column. How do I go about doing that? Thanks!
Input:
1 1 ABC DEF
2 2 IJK LMN
1 2 ZYX OPW
Output:
1 2 ZYX OPW (2 Replies)
Discussion started by: evelibertine
2 Replies
5. Shell Programming and Scripting
I have a list of 10 million page urls. I want those pages scraped and saved in the mysql database as raw html.
I own a Linux VPS server with 1GB RAM and WHM/cPanel.
I would like to scrape at least 100,000 urls in 24 hours.
So can anyone give me some sample shell scripting code? (4 Replies)
Discussion started by: Viruthagiri
4 Replies
6. Shell Programming and Scripting
Dear fellows, I need your help.
I'm trying to write a script to convert a single column into multiple rows.
But it need to recognize the beginning of the string and set it to its specific Column number.
Each Line (loop) begins with digit (RANGE).
At this moment it's kind of working, but it... (6 Replies)
Discussion started by: AK47
6 Replies
7. Shell Programming and Scripting
Dear community,
I have to split string in table and list all values. I'll skip the code and jump directly to mysql query.
This is the table:
category title
======= =======
7,3 title 1
1,3 title 2
1,2,3 title 3
Now, what I need is split category into single... (2 Replies)
Discussion started by: Lord Spectre
2 Replies
8. Shell Programming and Scripting
Hello,
I want to check the value of all MySQL columns.(column name is "status") via bash script.
If value is "0" at I want to make only single column value to "1"
I have many "0" values on mysql database(on "status" column)
"0" means it is a draft post. I want to publish a post. I... (2 Replies)
Discussion started by: tara123
2 Replies
9. UNIX for Dummies Questions & Answers
Hello,
some member created 27000 posts in wrong section (lol :D) so i need to edit all his entries to get new section ID.
SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%";
this will select all his topics...
the column with forum id is named "forum_id" and... (3 Replies)
Discussion started by: postcd
3 Replies
10. Programming
Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works:
UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` );
So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies
LEARN ABOUT CENTOS
dblink_build_sql_update
DBLINK_BUILD_SQL_UPDATE(3) PostgreSQL 9.2.7 Documentation DBLINK_BUILD_SQL_UPDATE(3)
NAME
dblink_build_sql_update - builds an UPDATE statement using a local tuple, replacing the primary key field values with alternative supplied
values
SYNOPSIS
dblink_build_sql_update(text relname,
int2vector primary_key_attnums,
integer num_primary_key_atts,
text[] src_pk_att_vals_array,
text[] tgt_pk_att_vals_array) returns text
DESCRIPTION
dblink_build_sql_update can be useful in doing selective replication of a local table to a remote database. It selects a row from the local
table based on primary key, and then builds a SQL UPDATE command that will duplicate that row, but with the primary key values replaced by
the values in the last argument. (To make an exact copy of the row, just specify the same values for the last two arguments.) The UPDATE
command always assigns all fields of the row -- the main difference between this and dblink_build_sql_insert is that it's assumed that the
target row already exists in the remote table.
ARGUMENTS
relname
Name of a local relation, for example foo or myschema.mytab. Include double quotes if the name is mixed-case or contains special
characters, for example "FooBar"; without quotes, the string will be folded to lower case.
primary_key_attnums
Attribute numbers (1-based) of the primary key fields, for example 1 2.
num_primary_key_atts
The number of primary key fields.
src_pk_att_vals_array
Values of the primary key fields to be used to look up the local tuple. Each field is represented in text form. An error is thrown if
there is no local row with these primary key values.
tgt_pk_att_vals_array
Values of the primary key fields to be placed in the resulting UPDATE command. Each field is represented in text form.
RETURN VALUE
Returns the requested SQL statement as text.
NOTES
As of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's
position in SELECT * FROM relname. Previous versions interpreted the numbers as physical column positions. There is a difference if any
column(s) to the left of the indicated column have been dropped during the lifetime of the table.
EXAMPLES
SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');
dblink_build_sql_update
-------------------------------------------------------------
UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'
(1 row)
PostgreSQL 9.2.7 2014-02-17 DBLINK_BUILD_SQL_UPDATE(3)