SQL 更新：您无法指定目标表“nilai_mhs”；用于 FROM 子句中的更新“

Question

我有这样的查询：

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”

有人可以帮助我吗？

Answer 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