Solved

change number randomly  mysql

Posted on 2010-11-19
19
370 Views
Last Modified: 2012-05-10
column a have for mat like 1-8205-01085-39-3    I want to change it randomly but it have link to column b and link to column c So i need to change colum b and c too to maintain link
0
Comment
Question by:teera
  • 9
  • 7
  • 2
  • +1
19 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34172627
can't understand this

>>it have link to column b and link to column c

what it means

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34172649
Hi,

If those 'b' and 'c' are foreign keys referencing the original column, you can create them using ON UPDATE CASCADE

http://dev.mysql.com/doc/refman/5.0/es/innodb-foreign-key-constraints.html


Hope it helps
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34172671
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:teera
ID: 34172826
Hi experts

    The number is a personal number of clubmember i want to create example database so i want to change that number
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34172876
Hi,

Please say whether those columns are referenced by any constraint or just by logic.

If they are not FK, just use

UPDATE tb1
SET a = newvalue

UPDATE tb2
SET b = newvalue

UPDATE tb3
SET c = newvalue


If they are, propagating the new data can be done using the constraint by using what I posted before.


Hope it's clear enough.

Cheers.
0
 

Author Comment

by:teera
ID: 34173057
Hi raulggonzalez

  I ant to change value for all value of column A with have 7000 record are there any sql staement to do this

Thank You
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34173106
Sure,

Do you need to keep the format as well ??

1-8205-01085-39-3  --> X-XXXX-XXXXX-XX-X

or just need a random number without format?

0
 

Author Comment

by:teera
ID: 34173117
Hi raulggonzalez

it should have for mat like  X-XXXX-XXXXX-XX-X
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34173128
well, dude what is column b and column c, and what you are actually looking for, sorry could not understand
0
 
LVL 32

Expert Comment

by:awking00
ID: 34173204
You might consider creating a trigger on update of column a that also updates columns b and c.
0
 

Author Comment

by:teera
ID: 34173223
Hi raulggonzalez

I want to change column A , B , C

such as  when i change value of column A from   1-8205-01085-39-3  to  8-1234-04585-39-8    all value of colum B and colum C that equal to 1-8205-01085-39-3 must change to 8-1234-04585-39-8

and I must change all record of column A

Thank
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34173329
Hi,

I think the best is to create and Stored Procedure to do the job, because yo have to loop through all those records....

I cannot try the syntax, you have to change table and column names... and I hope it works.


Cheers.


CREATE PROCEDURE curdemo()
BEGIN
  
  DECLARE tableID INT;
  DECLARE cur1 CURSOR FOR SELECT id FROM yourTable;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO tableID;
    UPDATE YourTable
		SET YourColumn = CONCAT (CAST(FLOOR(0 + (RAND() * 9)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9999)))AS CHAR , '-', 
						CAST(FLOOR(0 + (RAND() * 99999)))AS CHAR, '-' , 
						CAST(FLOOR(0 + (RAND() * 99)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9))))S CHAR)
	WHERE id = tableID
    
  END LOOP;

  CLOSE cur1;
END;

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34173368
Sorry I forgot about table B and C...

Are B and C in the same or in different tables...

the example is for different tables

if they are in the same, use this instead the 3 updates


UPDATE YourTable
            SET YourColumn = newValue,
                       B = newValue,
                       C = newValue
      WHERE id = tableID

Good luck

CREATE PROCEDURE curdemo()
BEGIN
  
  DECLARE tableID INT;
  DECLARE newValue CHAR(17);

  DECLARE cur1 CURSOR FOR SELECT id FROM yourTable;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO tableID;
    
    newValue = CONCAT (CAST(FLOOR(0 + (RAND() * 9)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9999)))AS CHAR , '-', 
						CAST(FLOOR(0 + (RAND() * 99999)))AS CHAR, '-' , 
						CAST(FLOOR(0 + (RAND() * 99)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9))))S CHAR);
						
    
    UPDATE YourTable
		SET YourColumn = newValue
	WHERE id = tableID
	
	UPDATE YourTable2
		SET YourColumn = newValue
	WHERE id = tableID
	
	UPDATE YourTable3
		SET YourColumn = newValue
	WHERE id = tableID
    
  END LOOP;

  CLOSE cur1;
END;

Open in new window

0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34173382
And put semi-colons after the UPDATE ...


Sorry I can't try the syntax ;)

0
 

Author Comment

by:teera
ID: 34179782

Hi experts

Error

SQL query:

CREATE PROCEDURE curdemo( ) BEGIN DECLARE tableID INT;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
CREATE PROCEDURE curdemo()
BEGIN
  
  DECLARE tableID INT;
  DECLARE newValue CHAR(17);

  DECLARE cur1 CURSOR FOR SELECT own FROM member;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO tableID;
    
    newValue = CONCAT (CAST(FLOOR(0 + (RAND() * 9)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9999)))AS CHAR , '-', 
						CAST(FLOOR(0 + (RAND() * 99999)))AS CHAR, '-' , 
						CAST(FLOOR(0 + (RAND() * 99)))AS CHAR , '-' , 
						CAST(FLOOR(0 + (RAND() * 9))))S CHAR);
						
    
    UPDATE member
            SET own = newValue,
                       fathercode = newValue,
                       mathercode = newValue
      WHERE own = tableID ;
    
  END LOOP;

  CLOSE cur1;
END;

Open in new window

c.PNG
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34179844
Sorry, I couldn't try the syntax...


this other is tested...

Hope it helps...

SELECT CONCAT (CAST(FLOOR(0 + (RAND() * 9))AS CHAR), '-' , 
						CAST(FLOOR(0 + (RAND() * 9999))AS CHAR) , '-', 
						CAST(FLOOR(0 + (RAND() * 99999))AS CHAR), '-' , 
						CAST(FLOOR(0 + (RAND() * 99))AS CHAR) , '-' , 
						CAST(FLOOR(0 + (RAND() * 9))AS CHAR))

Open in new window

0
 

Author Comment

by:teera
ID: 34179891
CREATE PROCEDURE curdemo()
BEGIN
 
  DECLARE tableID INT;
  DECLARE newValue CHAR(17);

  DECLARE cur1 CURSOR FOR SELECT own FROM member;

  OPEN cur1;

  read_loop: LOOP
    FETCH cur1 INTO tableID;
   
    newValue = SELECT CONCAT (CAST(FLOOR(0 + (RAND() * 9))AS CHAR), '-' ,
                                    CAST(FLOOR(0 + (RAND() * 9999))AS CHAR) , '-',
                                    CAST(FLOOR(0 + (RAND() * 99999))AS CHAR), '-' ,
                                    CAST(FLOOR(0 + (RAND() * 99))AS CHAR) , '-' ,
                                    CAST(FLOOR(0 + (RAND() * 9))AS CHAR)) ;
                                    
   
    UPDATE member
            SET own = newValue,
                       fathercode = newValue,
                       mathercode = newValue
      WHERE own = tableID ;
   
  END LOOP;

  CLOSE cur1;
END;
d.PNG
0
 
LVL 8

Accepted Solution

by:
raulggonzalez earned 500 total points
ID: 34179910
Hi,

You're lucky comparing to me...

Don't know what's wrong with this MySql , I'm getting errors much earlier than you :(

but I've executed just the select and you can see the result.

You can see in the screenshot that it's pointing syntax errors from the first DECLARE...

But the query works,  I swear, at least in my pc...


cheers random number with format
0
 

Author Closing Comment

by:teera
ID: 34180775
thank you
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question