SQL UPDATE : You can't specify target table 'nilai_mhs' for update in FROM clause "

0
votes

I have query like:

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')

but the result displays error:

You can't specify target table 'nilai_mhs' for update in FROM clause

Can anybody help me?

1
mysqlmysql-error-1093

1 Answers

1
votes

Just update the table directly without a subquery

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'

If you need to perform a subquery (for example you are groupping results) you need to join the subquery instead:

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