0

MYSQL triggers an SQLSTATE[42S02] error while processing a subquery with the table called through alias

"UPDATE call_log AS c1 INNER JOIN call_log AS c2 ON c1.id = c2.id SET c1.end ='".$reponse."' WHERE c1.id = (SELECT MAX(c2.id) FROM (SELECT * from c2 WHERE c2.num =".$numero." ) derivee )";

The desired outcome is to update the last matching line containing the $num variable in call_log table. As MYSQL prevents to do subqueries based on the same table I used aliases to refer to those tables as recommended here

mySQL error: #1248 - Every derived table must have its own alias

or here What is the error "Every derived table must have its own alias" in MySQL?

The Error I get is:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ERP.c2' doesn't exist'

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
JDEC
  • 93
  • 8
  • Am I correct in saying you are joining the table to itself? – Mark Sep 17 '19 at 15:28
  • Sample data, desired results, and an explanation of your logic would all help. – Gordon Linoff Sep 17 '19 at 15:37
  • I log into a database when the call starts and when it ends. The 'end' event happens through a eventlistener function. Of course I just want to update the field where the number called matches and where it is the last one. – JDEC Sep 17 '19 at 16:44
  • Still the same error. My database is indeed called ERP. The table is call_log with 2 aliases c1 and c2. What I do not get is why MYSQL does not understand that c2 is an alias of call_log and not a base table ? – JDEC Sep 18 '19 at 07:41

1 Answers1

0

OK, I found the issue. I changed slightly the command to simplify the request.

$sql ="UPDATE call_log as c1 SET c1.end = '".$reponse."' WHERE c1.id = (SELECT MAX(derive.id) FROM (SELECT * FROM call_log c2) derive WHERE derive.num =".$numero.")";

The main difference is that I create an alias for the table itself as c2 BUT I also use the alias of the (SELECT * FROM call_log c2) derive where derive will be the alias to filter the subquery results !!

If it helps others....

JDEC
  • 93
  • 8