Reputation: 130
DELETE tuser
FROM tuser
WHERE sts_seleksi IN (SELECT sts_seleksi
FROM tuser
LEFT JOIN ttes
ON ttes.id_user=tuser.id_user
WHERE sts_seleksi='1'
ORDER BY tuser.sts_verifikasi DESC,
ttes.nilai DESC,
tuser.id_user ASC
LIMIT 20)
It says
error #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
And then I tried
DELETE tuser
FROM tuser
INNER JOIN (SELECT sts_seleksi
FROM tuser
LEFT JOIN ttes
ON ttes.id_user=tuser.id_user
WHERE sts_seleksi='1'
ORDER BY tuser.sts_verifikasi DESC,
ttes.nilai DESC,
tuser.id_user ASC
LIMIT 20)
It still says:
error #1248 - Every derived table must have its own alias
mydata query = SELECT tuser.id_user,no_peserta,nisn,sts_verifikasi,sts_seleksi,ttes.nilai FROM
tuserLEFT JOIN ttes ON ttes.id_user=tuser.id_user WHERE sts_seleksi='1' ORDER BY tuser.sts_verifikasi DESC, ttes.nilai DESC, tuser.id_user ASC
id_user no_peserta nisn sts_verifikasi sts_seleksi nilai
6 U201601150006 2388881 1 1 NULL
7 U201601180007 129811 1 1 NULL
8 U201602090008 2788923 1 1 NULL
10 U201602090010 3434881 1 1 NULL
55 U201602100055 2221119 1 1 NULL
56 U201602100056 2111100 1 1 NULL
57 U201602100057 2999323 1 1 NULL
58 U201602100058 210091 1 1 NULL
59 U201602100059 230034 1 1 NULL
60 U201602100060 2009149 1 1 NULL
20 U201602090020 2111343 0 1 99.99
2 U201601100002 128899 0 1 30.00
3 U201601100003 238239 0 1 20.00
4 U201601110004 82371 0 1 20.00
12 U201602100012 433121 0 1 10.00
1 U201601100001 9012291 0 1 NULL
5 U201601150005 2332311 0 1 NULL
9 U201602090009 9231212 0 1 NULL
11 U201602090011 531211 0 1 NULL
13 U201602090013 3455532 0 1 NULL
14 U201602090014 234332 0 1 NULL
15 U201602090015 2339905 0 1 NULL
16 U201602090016 2211236 0 1 NULL
17 U201602090017 3234378 0 1 NULL
18 U201602090018 2211235 0 1 NULL
19 U201602090019 2145563 0 1 NULL
21 U201602090021 2754317 0 1 NULL
Upvotes: 2
Views: 601
Reputation: 72235
You have to alias the derived table used in the INNER JOIN
operation:
DELETE tuser
FROM tuser
INNER JOIN (
SELECT id_user
FROM tuser
LEFT JOIN ttes ON ttes.id_user=tuser.id_user
WHERE sts_seleksi='1'
ORDER BY tuser.sts_verifikasi DESC, ttes.nilai DESC, tuser.id_user ASC
LIMIT 20
) AS tuser2 ON tuser.id_user = tuser2.id_user
You also have to provide the ON
clause. Your sample data suggest that field id_user
should be used there.
Upvotes: 2
Reputation: 1271241
Just add the alias . . . and an ON
clause as well:
DELETE tuser
FROM tuser INNER JOIN
(SELECT sts_seleksi
FROM tuser LEFT JOIN
ttes
ON ttes.id_user = tuser.id_user
WHERE sts_seleksi='1'
ORDER BY tuser.sts_verifikasi DESC,
ttes.nilai DESC,
tuser.id_user ASC
LIMIT 20
) tt
ON tuser.sts_seleksi = tt.sts_seleksi;
Upvotes: 0