Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

change number randomly  mysql

Posted on 2010-11-19
19
Medium Priority
?
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

704 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