Sponsored Content
Top Forums Web Development Mysql question: Best way to update a column containing 8 million rows Post 302357837 by Scott on Wednesday 30th of September 2009 06:37:36 PM
Old 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

[MYSQL] problem with spaces in rows

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

awk command to print only selected rows in a particular column specified by column name

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

merging rows into new file based on rows and first column

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

[Solved] Deleting all rows where the first column equals the second column

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

Scrape 10 million pages and save the raw html data in mysql database

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

Converting Single Column into Multiple rows, but with strings to specific tab column

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

MySql split rows

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

Update a mysql column via bash script

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

Mysql: How to update value in 27000 rows?

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

MYSQL - trigger update on record insert or update

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
GIT-RESET(1)							    Git Manual							      GIT-RESET(1)

NAME
git-reset - Reset current HEAD to the specified state SYNOPSIS
git reset [-q] [<tree-ish>] [--] <paths>... git reset (--patch | -p) [<tree-sh>] [--] [<paths>...] git reset [--soft | --mixed | --hard | --merge | --keep] [-q] [<commit>] DESCRIPTION
In the first and second form, copy entries from <tree-ish> to the index. In the third form, set the current branch head (HEAD) to <commit>, optionally modifying index and working tree to match. The <tree-ish>/<commit> defaults to HEAD in all forms. git reset [-q] [<tree-ish>] [--] <paths>... This form resets the index entries for all <paths> to their state at <tree-ish>. (It does not affect the working tree, nor the current branch.) This means that git reset <paths> is the opposite of git add <paths>. After running git reset <paths> to update the index entry, you can use git-checkout(1) to check the contents out of the index to the working tree. Alternatively, using git-checkout(1) and specifying a commit, you can copy the contents of a path out of a commit to the index and to the working tree in one go. git reset (--patch | -p) [<tree-ish>] [--] [<paths>...] Interactively select hunks in the difference between the index and <tree-ish> (defaults to HEAD). The chosen hunks are applied in reverse to the index. This means that git reset -p is the opposite of git add -p, i.e. you can use it to selectively reset hunks. See the "Interactive Mode" section of git-add(1) to learn how to operate the --patch mode. git reset [<mode>] [<commit>] This form resets the current branch head to <commit> and possibly updates the index (resetting it to the tree of <commit>) and the working tree depending on <mode>. If <mode> is omitted, defaults to "--mixed". The <mode> must be one of the following: --soft Does not touch the index file nor the working tree at all (but resets the head to <commit>, just like all modes do). This leaves all your changed files "Changes to be committed", as git status would put it. --mixed Resets the index but not the working tree (i.e., the changed files are preserved but not marked for commit) and reports what has not been updated. This is the default action. --hard Resets the index and working tree. Any changes to tracked files in the working tree since <commit> are discarded. --merge Resets the index and updates the files in the working tree that are different between <commit> and HEAD, but keeps those which are different between the index and working tree (i.e. which have changes which have not been added). If a file that is different between <commit> and the index has unstaged changes, reset is aborted. In other words, --merge does something like a git read-tree -u -m <commit>, but carries forward unmerged index entries. --keep Resets index entries and updates files in the working tree that are different between <commit> and HEAD. If a file that is different between <commit> and HEAD has local changes, reset is aborted. If you want to undo a commit other than the latest on a branch, git-revert(1) is your friend. OPTIONS
-q, --quiet Be quiet, only report errors. EXAMPLES
Undo add $ edit (1) $ git add frotz.c filfre.c $ mailx (2) $ git reset (3) $ git pull git://info.example.com/ nitfol (4) 1. You are happily working on something, and find the changes in these files are in good order. You do not want to see them when you run "git diff", because you plan to work on other files and changes with these files are distracting. 2. Somebody asks you to pull, and the changes sounds worthy of merging. 3. However, you already dirtied the index (i.e. your index does not match the HEAD commit). But you know the pull you are going to make does not affect frotz.c nor filfre.c, so you revert the index changes for these two files. Your changes in working tree remain there. 4. Then you can pull and merge, leaving frotz.c and filfre.c changes still in the working tree. Undo a commit and redo $ git commit ... $ git reset --soft HEAD^ (1) $ edit (2) $ git commit -a -c ORIG_HEAD (3) 1. This is most often done when you remembered what you just committed is incomplete, or you misspelled your commit message, or both. Leaves working tree as it was before "reset". 2. Make corrections to working tree files. 3. "reset" copies the old head to .git/ORIG_HEAD; redo the commit by starting with its log message. If you do not need to edit the message further, you can give -C option instead. See also the --amend option to git-commit(1). Undo a commit, making it a topic branch $ git branch topic/wip (1) $ git reset --hard HEAD~3 (2) $ git checkout topic/wip (3) 1. You have made some commits, but realize they were premature to be in the "master" branch. You want to continue polishing them in a topic branch, so create "topic/wip" branch off of the current HEAD. 2. Rewind the master branch to get rid of those three commits. 3. Switch to "topic/wip" branch and keep working. Undo commits permanently $ git commit ... $ git reset --hard HEAD~3 (1) 1. The last three commits (HEAD, HEAD^, and HEAD~2) were bad and you do not want to ever see them again. Do not do this if you have already given these commits to somebody else. (See the "RECOVERING FROM UPSTREAM REBASE" section in git-rebase(1) for the implications of doing so.) Undo a merge or pull $ git pull (1) Auto-merging nitfol CONFLICT (content): Merge conflict in nitfol Automatic merge failed; fix conflicts and then commit the result. $ git reset --hard (2) $ git pull . topic/branch (3) Updating from 41223... to 13134... Fast-forward $ git reset --hard ORIG_HEAD (4) 1. Try to update from the upstream resulted in a lot of conflicts; you were not ready to spend a lot of time merging right now, so you decide to do that later. 2. "pull" has not made merge commit, so "git reset --hard" which is a synonym for "git reset --hard HEAD" clears the mess from the index file and the working tree. 3. Merge a topic branch into the current branch, which resulted in a fast-forward. 4. But you decided that the topic branch is not ready for public consumption yet. "pull" or "merge" always leaves the original tip of the current branch in ORIG_HEAD, so resetting hard to it brings your index file and the working tree back to that state, and resets the tip of the branch to that commit. Undo a merge or pull inside a dirty working tree $ git pull (1) Auto-merging nitfol Merge made by recursive. nitfol | 20 +++++---- ... $ git reset --merge ORIG_HEAD (2) 1. Even if you may have local modifications in your working tree, you can safely say "git pull" when you know that the change in the other branch does not overlap with them. 2. After inspecting the result of the merge, you may find that the change in the other branch is unsatisfactory. Running "git reset --hard ORIG_HEAD" will let you go back to where you were, but it will discard your local changes, which you do not want. "git reset --merge" keeps your local changes. Interrupted workflow Suppose you are interrupted by an urgent fix request while you are in the middle of a large change. The files in your working tree are not in any shape to be committed yet, but you need to get to the other branch for a quick bugfix. $ git checkout feature ;# you were working in "feature" branch and $ work work work ;# got interrupted $ git commit -a -m "snapshot WIP" (1) $ git checkout master $ fix fix fix $ git commit ;# commit with real log $ git checkout feature $ git reset --soft HEAD^ ;# go back to WIP state (2) $ git reset (3) 1. This commit will get blown away so a throw-away log message is OK. 2. This removes the WIP commit from the commit history, and sets your working tree to the state just before you made that snapshot. 3. At this point the index file still has all the WIP changes you committed as snapshot WIP. This updates the index to show your WIP files as uncommitted. See also git-stash(1). Reset a single file in the index Suppose you have added a file to your index, but later decide you do not want to add it to your commit. You can remove the file from the index while keeping your changes with git reset. $ git reset -- frotz.c (1) $ git commit -m "Commit files in index" (2) $ git add frotz.c (3) 1. This removes the file from the index while keeping it in the working directory. 2. This commits all other changes in the index. 3. Adds the file to the index again. Keep changes in working tree while discarding some previous commits Suppose you are working on something and you commit it, and then you continue working a bit more, but now you think that what you have in your working tree should be in another branch that has nothing to do with what you committed previously. You can start a new branch and reset it while keeping the changes in your working tree. $ git tag start $ git checkout -b branch1 $ edit $ git commit ... (1) $ edit $ git checkout -b branch2 (2) $ git reset --keep start (3) 1. This commits your first edits in branch1. 2. In the ideal world, you could have realized that the earlier commit did not belong to the new topic when you created and switched to branch2 (i.e. "git checkout -b branch2 start"), but nobody is perfect. 3. But you can use "reset --keep" to remove the unwanted commit after you switched to "branch2". DISCUSSION
The tables below show what happens when running: git reset --option target to reset the HEAD to another commit (target) with the different reset options depending on the state of the files. In these tables, A, B, C and D are some different states of a file. For example, the first line of the first table means that if a file is in state A in the working tree, in state B in the index, in state C in HEAD and in state D in the target, then "git reset --soft target" will leave the file in the working tree in state A and in the index in state B. It resets (i.e. moves) the HEAD (i.e. the tip of the current branch, if you are on one) to "target" (which has the file in state D). working index HEAD target working index HEAD ---------------------------------------------------- A B C D --soft A B D --mixed A D D --hard D D D --merge (disallowed) --keep (disallowed) working index HEAD target working index HEAD ---------------------------------------------------- A B C C --soft A B C --mixed A C C --hard C C C --merge (disallowed) --keep A C C working index HEAD target working index HEAD ---------------------------------------------------- B B C D --soft B B D --mixed B D D --hard D D D --merge D D D --keep (disallowed) working index HEAD target working index HEAD ---------------------------------------------------- B B C C --soft B B C --mixed B C C --hard C C C --merge C C C --keep B C C working index HEAD target working index HEAD ---------------------------------------------------- B C C D --soft B C D --mixed B D D --hard D D D --merge (disallowed) --keep (disallowed) working index HEAD target working index HEAD ---------------------------------------------------- B C C C --soft B C C --mixed B C C --hard C C C --merge B C C --keep B C C "reset --merge" is meant to be used when resetting out of a conflicted merge. Any mergy operation guarantees that the working tree file that is involved in the merge does not have local change wrt the index before it starts, and that it writes the result out to the working tree. So if we see some difference between the index and the target and also between the index and the working tree, then it means that we are not resetting out from a state that a mergy operation left after failing with a conflict. That is why we disallow --merge option in this case. "reset --keep" is meant to be used when removing some of the last commits in the current branch while keeping changes in the working tree. If there could be conflicts between the changes in the commit we want to remove and the changes in the working tree we want to keep, the reset is disallowed. That's why it is disallowed if there are both changes between the working tree and HEAD, and between HEAD and the target. To be safe, it is also disallowed when there are unmerged entries. The following tables show what happens when there are unmerged entries: working index HEAD target working index HEAD ---------------------------------------------------- X U A B --soft (disallowed) --mixed X B B --hard B B B --merge B B B --keep (disallowed) working index HEAD target working index HEAD ---------------------------------------------------- X U A A --soft (disallowed) --mixed X A A --hard A A A --merge A A A --keep (disallowed) X means any state and U means an unmerged index. GIT
Part of the git(1) suite Git 1.8.3.1 06/10/2014 GIT-RESET(1)
All times are GMT -4. The time now is 06:44 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy