我们可以在更新查询中使用两个临时表吗... [英] Can we use two temporary tables in update query...

问题描述

我的更新查询是

my update query is

UPDATE student_attendance sa,
  (SELECT DISTINCT
     sm.reg_number,
     sm.id,
     sm.cur_academic_id,
     ctm.term_id,
     tmp.Percentage
   FROM tmp_attendancestatus_idcoperator131781 AS tmp,
     students_master sm,
     course_term_master ctm
   WHERE tmp.HallticketNumber = sm.reg_number
       AND ctm.term_id = sm.current_term_id
       AND ctm.semyear = tmp.Year
       AND ctm.semterm = tmp.Semester
       AND TRIM(STATUS) = 'Success'
       AND sm.college_id = 40) AS sd,
  tmp_attendancestatus_idcoperator131781 AS tmpup
SET tmpup.tmpstatus = 1
WHERE sa.student_id = sd.id
    AND sa.academic_id = sd.cur_academic_id
    AND sa.term_id = sd.term_id
    AND sa.attendance_percentage = sd.Percentage
    AND tmpup.HallticketNumber = sd.reg_number

我在更新查询中使用两个临时表...但它告诉安装错误 - 无法重新打开临时表...给我替代解决方案对于此查询...

Im using two temporary tables in a update query...but its telling ann error- "cannot reopen the temp table"...give me alternate solution for this query...

推荐答案

Quote：

错误1137：无法重新打开表：''name''



如果多次引用临时表，也会发生此错误即使引用出现在函数内的不同语句中，也可以在不同的别名下存储函数。

ERROR 1137: Can''t reopen table: ''name''

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

请阅读： http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html [ ^ ]和：http://bugs.mysql.com/bug.php?id=10327 [ ^ ]

这篇关于我们可以在更新查询中使用两个临时表吗...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！