CODEWITHSUNDEEP
mysqlsqldelete-row
Hafidh
Hafidh

Reputation: 130

delete sql using left join, order and limit

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 FROMtuserLEFT 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

Answers (2)

Giorgos Betsos
Giorgos Betsos

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.

Demo here

Upvotes: 2

Gordon Linoff
Gordon Linoff

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

Related Questions