[RESOLVED] Unable to figure out query error - please help

ppowell

Now I have another SQL query for MySQL I can't figure out!! This is overwhelming me completely and I also must have this figured out today and I can't figure it out!!

UPDATE student_db.student
SET has_letter1 = (
SELECT i.letter1 FROM olddb.student i, student_db student s WHERE s.unique_key = i.unique_key
)

This query produces the following error:

You can't specify target table 'student' for update in FROM clause

I can't figure this one out and am required to do so rather urgently (as in before COB today)

Thanx
Phil

Drakla

Mr Manual - him do say:
"Currently, you cannot update a table and select from the same table in a subquery."

PS. What does cob mean?

ppowell

OK that is bad because that's exactly what I have to do! 🙁

Phil

tomhath

Note sure if MySQL will take this, but give it a try. What you had wouldn't give the desired result anyway (you need to join back to the row that's being updated, the way you had it there was no connection to the row being updated and the subselect):

UPDATE student_db.student
SET has_letter1 = (
SELECT olddb.student.letter1 
FROM olddb.student 
WHERE student_db.student.unique_key = olddb.student.unique_key
)

ppowell

Yep, and I even made an easier-to-read version of it:

UPDATE student_db.student s
SET s.has_letter1 = (
 SELECT ica.has_letter1
 FROM olddb.applicant_completion ica, olddb.applicant i
 WHERE ica.applicant_id = i.id
   AND s.unique_key = i.unique_key
)

Basically I forgot to do the proper joins and then I reconfigured it to your structure and it worked!

Thanx
Phil

tomhath

That still doesn't look right.

How does it know which unique_key to use in the subselect? You have joined olddb.applicant_completion ica to olddb.applicant, but there's no connection to student_db.student.

Look again at what I posted, you need student_db.student in the WHERE clause.

ppowell

Sorry to tell you this, but it works! I can't put student_db.student in the sub-select as that's illegal in an update with a sub-select in MySQL, so, bluntly put, it works, I can't figure out why and that's about all I can handle for now.

Phil