Solved

change number randomly  mysql

Posted on 2010-11-19
19
365 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Fixed Length SQL Query Question 3 28
MS SQL - Rotating Values in SQL 9 50
Make query more efficient 1 16
email about the whoisactive result 7 23
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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