Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

change number randomly  mysql

Posted on 2010-11-19
19
Medium Priority
?
378 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:Muhammad Ahmad Imran
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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:Muhammad Ahmad Imran
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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

886 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