首页 文章

SQL更新：您可以't specify target table ' nilai_mhs'在FROM子句中进行更新“

提问于
浏览 次
0

我有这样的查询：

UPDATE nilai_mhs 
SET status_nilai = 3
where 
id_kode_mk = 
(SELECT e.id_kode_mk
FROM nilai_mhs c, tbl_mhs d,master_mk e,tbl_mk_pertahun f
WHERE 
c.nim = d.nim
AND 
c.id_kode_mk = e.id_kode_mk
AND
e.id_kode_mk = f.id_kode_mk
AND 
c.nim = '20081001010125'
AND 
c.status_nilai =2
AND
sem_ganjil_genap = 'ganjil')

但结果显示错误：

您无法在FROM子句中为更新指定目标表'nilai_mhs'

有谁能够帮我？

mysql mysql-error-1093

1 回答

  • 1

    只需在没有子查询的情况下直接更新表

    UPDATE nilai_mhs c
  JOIN tbl_mhs d USING(nim)
  JOIN master_mk e USING(id_kode_mk)
  JOIN tbl_mk_pertahun f USING(id_kode_mk)
SET c.status_nilai = 3
WHERE c.nim = '20081001010125'
  AND c.status_nilai = 2
  AND sem_ganjil_genap = 'ganjil'

    如果您需要执行子查询（例如，您正在灌输结果），则需要加入子查询：

    UPDATE nilai_mhs t JOIN (
    SELECT e.id_kode_mk
    FROM nilai_mhs c, tbl_mhs d,master_mk e,tbl_mk_pertahun f
    WHERE c.nim = d.nim AND c.id_kode_mk = e.id_kode_mk AND e.id_kode_mk = f.id_kode_mk AND c.nim = '20081001010125' AND c.status_nilai =2 AND sem_ganjil_genap = 'ganjil'
) q ON t.id_kode_mk = q.id_kode_mk
SET t.status_nilai = 3
    回复于

相关问题