loops in MySQL

Strikerz95 · #1 (**permalink**) 06-09-04, 16:26

Is there any looping commands in MySQL. I need to go through a bunch of rows and check certain fields against all other existing fields in the table, and if a duplicate field exists, I will then need to change that field based on other data in the table. Something like a "for" loop or a "do while" loop would be nice.

r937 · #2 (**permalink**) 06-09-04, 19:42

sorry, no such thing

use a flat file if you want to loop

databases are for set-at-a-time processing, not record-at-a-time

if you describe your requirements a little bit, we might be able to help you

Strikerz95 · #3 (**permalink**) 06-14-04, 12:57

Ok, well here is a small script that I am running that pulls basic data from a table of students and outputs each students data to a txt file in a specific format that can then be imported into some web portal software called blackboard:

Code:

SELECT DISTINCT concat_ws(       
	 '',
	 '"',
	 (	 
	    concat_ws(            
 	      '","',
	      concat_ws('',LEFT(first_name,1),last_name),
	      last_name,
	      first_name,
	      concat_ws('',LEFT(first_name,1),last_name,'@','christianheritage.edu'),
	      concat_ws('',LOWER(LEFT(first_name,1)),LOWER(LEFT(last_name,1)),student_id),
	      student_id,
	      middle_name
            )
         ),
         '"'
       )
FROM chc_students INTO OUTFILE "/blackboard_create_users.txt";

The outputed txt file contains lines that look like this:

Code:

"AAnderson","Anderson","Alexia","AAnderson@christianheritage.edu","aa113732","113732","C"
"DBlair","Blair","Daniel","DBlair@christianheritage.edu","db113734","113734","K"
"JWest","West","Jonathan","JWest@christianheritage.edu","jw113717","113717","G"
"KGoodhart","Goodhart","Karen","KGoodhart@christianheritage.edu","kg113710","113710","M"
"MSaechao","Saechao","May","MSaechao@christianheritage.edu","ms113715","113715","C"
"DWatson","Watson","Debra","DWatson@christianheritage.edu","dw113672","113672","L"

Each line contains information for creating an account in the web portal software. The first field is the student's username (first initial + lastname). Since there are hundreds of students, there will most definitely be duplicate usernames, which poses a problem. So I need a method to check each record against all other records, and if a duplicate exists, alter the current record's username with something like a middle initial as well or a one at the end of the user's name.

I have programmed C before and understand how I could do it there, but I am relatively new to SQL. Thanks for the help,

Brian

Web Administrator
Shadow Mountain Ministries

anacedent · #4 (**permalink**) 06-14-04, 13:07

I use PERL (via DBD/DBI) to this type of work.

r937 · #5 (**permalink**) 06-14-04, 16:46

add an ORDER BY clause to your SELECT, and post-process the output file

alternatively, if you want to assign unique userids in your chc_students table, there's another SELECT you can run to tell you which ones will have duplicates, if you decide you want to maintain the uniqueness in that table from now on

skinnywhitegeek · #6 (**permalink**) 06-16-04, 09:56

I'm having a similar issue.

I have been using PHP with a browser to select all records, modify/format some varchar data, and insert the formatted data into a different date field, one record at a time. (i'm basically repairing a poorly planned database so that it is usable) It is really slow to update thousands of records with concatenated information this way.

How do you recommend I "post-process the output file"?

I dont have control over whats installed on the server which hosts my mysql database. I just ssh'd in to check processes with the "top" command, and mysql is peaking the processor out at 98-99 percent, during my php UPDATE query process, one for each row in my resultset.

Thanks,

G

Internet Services

Web Development

Digital Marketing

Consumer Tech