Solved

change number randomly  mysql

Posted on 2010-11-19
19
359 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 31

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now