0

I've been asked to go through a former coworker's code, and for the life of me, I can't figure this query out. It's extracted from PHP, hence the way variables and quotes are used, but it's the query that I'm trying to figure out.

"UPDATE item SET shipment_id = '' WHERE id IN ( SELECT id FROM (SELECT id FROM item WHERE purchase = ".$purchase." AND repair_status <> 4 ORDER BY id ASC LIMIT 0, ".$count.") tmp ) AND purchase = ".$purchase." AND repair_status <> 4"

Separating everything out with a bit of psuedocode for clarity, I get this:

UPDATE item SET shipment_id = '' WHERE id IN ( subquery1 ) AND purchase = ".$purchase." AND repair_status <> 4;

subquery1:
SELECT id FROM (subquery2) tmp

subquery2:
SELECT id FROM item WHERE purchase = ".$purchase." AND repair_status <> 4 ORDER BY id ASC LIMIT 0, ".$count."

The one I've dubbed "subquery1" is what I don't understand. We don't have any tables or fields named 'tmp', and I've gone over the mysql reserved words - it's not there either. He's no longer around to ask him, so that's not an option. What could possibly be going on here?

Fibericon
  • 5,684
  • 12
  • 37
  • 64
  • 1
    It's an alias for the subquery. It's required by the MySQl syntax, even though it might not actually be used. Think of it as `SELECT someColumn FROM someTable AS tmp` – Tangentially Perpendicular Mar 15 '23 at 03:45
  • I believe it you two are right. I always thought 'as' was a required part of using an alias, so that never occurred to me. – Fibericon Mar 15 '23 at 03:48

0 Answers0