MySQL Forums
Forum List  »  Newbie

Re: UPDATE a field SEVERAL times in 1 UPDATE
Posted by: Bigcrime Crimebig
Date: August 19, 2010 11:02AM

CREATE TABLE accounts (
id INT( 12 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
users VARCHAR( 12 ) NOT NULL ,
trans INT( 6 ) NULL
relationed VARCHAR( 12 ) NOT NULL ,
fills INT( 2 ) NULL
) ENGINE = MYISAM ;


id......users......trans.....relationed.....fills
1.......David......5.........Carl................0
2.......John.......6.........Carl................0
3.......Carl.......2.........nobody..............1
4.......Liss.......4.........David...............0

NOTE: Remamber that this is a short list, they are over 2,000,000 users.

/*Like this I take the data Ill update*/
SELECT Relationed FROM accounts WHERE fills=0;
/*here update*/
UPDATE accounts SET trans = trans+1 WHERE users IN ('Carl','Carl','David');

Result I want:

id......users......trans.....relationed.....fills
1.......David......6.........Carl................0
2.......John.......6.........Carl................0
3.......Carl.......4.........nobody..............1
4.......Liss.......4.........David...............0

As you can see I select relationed with fills = 0, then with PHP I create the update and each result I received from SELECT I put them in order into IN (...).

PHP doesnt know which one is repeated and to do something like that is a SUPER DELAY, cuz ill have to compare each result with others and create others updates for relationed > 1.

I dont know how it will help you but you said youll help me If I do this, Ill be waiting your help ^_^! or other helps.

Thanks!



Edited 5 time(s). Last edit at 08/19/2010 07:21PM by Bigcrime Crimebig.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.