New Value From Concatenated Values?
Is there a way to update a table so that a column's values can be changed to a concatenation of two other column values? For instance, something like ... UPDATE TABLE tablename SET colA = colB.colC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreate Table With Sorted Data
Embarrassingly simple answer to the question why I didn't just use a View ... I haven't gotten to that chapter in the manual yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recreate Table With Sorted Data
I have a table with 1600 student locks in random order. I would like them permanently sorted by the locker number they are assigned to. I assumed that ... ~ I would copy the table under a different name ~ Delete all records from this copy ~ Write a statement that would copy the records from the original table into the copied table in SORTED order ~ Delete the original table ~ Rename the copy to the same name as the original Question 1: Is there a better way to get the desired result? Question 2: If not, what would the Insert/Select statement look like that would copy the records over in sorted order? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find and Add Unmatched Records
I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables.
RE: Find and Add Unmatched Records
Same situation, but let's say it's not preceding_quarter and current_quarter for the table names. To make the flow of records more clear, let's say the one I was calling preceding_quarter is now called keepers and the one I called current_quarter is now called pickThroughThenDelete. I'll delete the pickThroughThenDelete table after I've found and transferred the new students. Would this syntax work? insert into keepers select pickThroughThenDelete.* from pickThroughThenDelete left outer join keepers using(student_id) where keepers.student_id is null; drop table pickThroughThenDelete; -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Friday, June 01, 2007 10:35 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Find and Add Unmatched Records Hi John, Kebbel, John wrote: I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. It sounds to me like you want an exclusion join: insert into new_students(...) select current_quarter.* from current_quarter left outer join preceding_quarter using(student_id) where preceding_quarter.student_id is null; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Moving Data between Tables
I moved information about our school locks (serial numbers, combinations, student, etc) from FileMaker to MySQL into a table called lockers and wrote PHP pages so our teachers could record the locks returned at the end of the year. Unfortunately, I missed transferring close to 200 locks. I duplicated the structure of lockers (1313 locks) as lockers2 and reloaded all the information from Filemaker (1492 locks). My problem is moving the information from lockers to lockers2 that was added AFTER the Filemaker migration (2nd semester teacher, returned, paid). This is what I'm getting set to try, but I've never seen a JOIN in an update statement before. Am I on the right track for this? UPDATE lockers2 SET lockers2.returned = lockers.returned, lockers2.teacher2nd = lockers.teacher2nd, lockers2.paid = lockers.paid WHERE lockers2.serialNumber = lockers.serialNumber; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving Data between Tables
I hope you're not about to try this for the first time on your production data :-) I dumped the lockers table before beginning my experiments. If I had trashed lockers2 (my experimental file) in the process, I would have truncated it and re-inserted the 1492 records from my batch file. - It's not quite right. You're correct. I tried my UPDATE/implicit JOIN query shortly after posting my email. I expected the query to fail, which it did. Not knowing how long it would be before someone responded, I went ahead and solved my problem with a PHP script. It was nice that the problem was solve-able with PHP, but it was frustrating to write 20 lines of PHP because I didn't know how to write the 1 line of MySQL that would have solved my problem. - update foo inner join bar on ... set a = b, c = d... I run a backup of the database and associated PHP files every few nights and then carry a copy of the backup home to expand the PHP and incorporate the new things I learn about MySQL into my table structures or into batch files. You can be assured I'll be spending time with our old friends foo and bar and this update syntax between now and Monday. Thanks again, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --xml or --html output to file
Dan and Paul, I develop at home on Ubuntu Linux and deploy at work over FreeBSD Unix (an iMac running OS 10.4). The command line tip you suggested worked fine on Linux, so I'm assuming that I can get it to run here at work as well when I get the time to try it. Thanks again. John Another Brick in the Wall Kebbel
Permissions and Into Outfile
I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written? This is the query I was using... mysql select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights into outfile '/tmp/srhs9.txt' from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Permissions and Into Outfile
cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home) shows me this for mysql ... mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false The home directory for mysql is /var/empty, which does exist. Should I write ~into file~ output to this folder or should I change the mysql home directory to some other folder? -- From: Gerald L. Clark Sent: Friday, April 27, 2007 10:09 AM To: Kebbel, John Cc: mysql@lists.mysql.com Subject: Re: Permissions and Into Outfile Kebbel, John wrote: I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written? This is the query I was using... mysql select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights into outfile '/tmp/srhs9.txt' from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname; The user 'mysql' must have write permission in the target directory. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Categories of Users and Privileges
Up to this point, I have controlled MySQL privileges from Perl or PHP pages. All connections to the database used the same MySQL login. Ordinary users used pages that strictly controlled selections, insertions, or updates. Administrators/Developers used password-protected pages that allowed deletions and broader selections, insertions, and updates. While reading and studying for the MySQL certification exam, I began thinking about controlling privileges from within MySQL by having different pages using different MySQL logins. I have a lot of resources that explain how to grant privileges, but does anyone know of a resource that discusses categories of users and the pros/cons of the various privileges that should be assigned to them? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is /*!40101 SET @...
I've been developing Perl/ PHP and MySQL applications on a small scale for years, reworking my small core of knowledge over and over again. I recently decided to hit the books and shoot for certification as a MySQL CMDEV. Most of my questions have answers in my books and on web sites, but I occassionally run into stuff so weird, I don't even know how to phrase a web query. For example, what is this? It appears at the top of a data dump. I recognize the Create and Insert sections, but this is Greek. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
