[RESOLVED] super-simple update I can't do - please help

ppowell

Consider these tables

student

id int not null auto_increment, primary key (id),
school_year_id int not null,
unique_key varchar(16) not null

interns

id int not null auto_increment, primary key (id),
enrollment_status varchar(40) not null,
unique_key varchar(16) not null

school_year

id int not null auto_increment, primary key (id),
school_year_name varchar(40) null

OK what I want to do is extremely easy, but beyond my ability to write this (I need a DBA!)

Foreach record in student I want to put in the field column school_year_id the value of school_year.id where student.unique_key = interns.unique_key AND upper(interns.enrollment_status) = upper(school_year.school_year_name).

I hope that makes sense, but that's as clear as I can do it w/o a whiteboard to show you exactly what I want.

Sorry, I simply can't do something like this, I've tried for weeks to no avail.

Thanx
Phil

bogu

First do a selection:

SELECT id FROM school_year, interns, student
where student.unique_key = interns.unique_key AND upper(interns.enrollment_status) = upper(school_year.school_year_name)

After that u get your id and inserted into your table, but I can say that something isnt right there, u should check your tables structure and rethink them (just a sugestion) ...

Roger_Ramjet

I dunno what is going on here, but the query is

$sql = "UPDATE student 
     INNER JOIN interns USING(unique_key) 
     INNER JOIN school_year ON(interns.enrollment_status=school_year.school_year_name) 
     SET school_year_id = school_year.id";

The joins take care of your where conditions.

One thing you must note is that all the columns used in the joins should be indexed.
It would also be more efficient to be storing the ids in the tables instead of the varchar columns. That is why Bogu said that you may want to revise your table layout. I'm not sure where the unique_key varchar columns in students and interns comes from, but if they match in both tables then my guess is that there is another table somewhere that defines them, so use the id column from that in both these tables.

interns

id int not null auto_increment, primary key (id),
year_id int not null,
unique_key varchar(16) not null

school_year

year_id int not null auto_increment, primary key (id),
school_year_name varchar(40) null

PS you don't need to worry about converting things to upper-case unless you have set a case sensitive collation on your db.

ppowell

Actually, in the new "student" table, they are referenced by ID, but I had to bundle the old data first in the "interns" table, which was never indexed in the first place.

I got the help I needed by doing this

update student s, interns i, school_year y
set s.school_year_id = y.id
where s.unique_key = i.unique_key
and s.student_first_name = i.fname
and s.student_last_name = i.lname
and s.email = i.email
and lower(i.enrollment_year) = lower(y.school_year_name)

Phil

Roger_Ramjet

Both work but my query is more efficient. You would not notice it with small tables. Still, it is a fundamental that explicit joins on indexed columns are more efficient than natural joins which relie on where conditions, especially with mysql which operates one-pass joining. Explicit joins also help the query optimiser produce an execution plan quickly.

ppowell

Oh BTW, there is no other table the references unique_key. That is a random 16-character string that is auto-generated by a PHP function that ensures repost protection when a student applies and accidentally hits the "submit" button more than once.

And while the student table again has "foreign key" id columns (not real ones as these have to be MyISAM tables for another requirement), the old "interns" table does not - and cannot - have them.

Phil