CODEWITHSUNDEEP
javamysqlservletstry-catchresultset
Tom Smith
Tom Smith

Reputation: 19

I'm not able to delete all rows in mysql table

Hello I want to delete all rows in my mysql table called "kosik" and pass them to another table called "obj_zoznam".

I'm using this code in java:

String orderstatus = "Accepted";
        try {
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            stmt3 = con.createStatement();

            rs = stmt.executeQuery("select ID as idzaz, ID_pouzivatela as idpou, ID_tovaru as idtov, cena as suma, ks as kusy from kosik order by ID limit 1");

            while (rs.next()) {

                int zaz = rs.getInt("idzaz");
                int pou = rs.getInt("idpou");
                int tov = rs.getInt("idtov");
                int suma = rs.getInt("suma");
                int kusy = rs.getInt("kusy");

                String sstr = "insert into obj_zoznam (ID_pouzivatela, ID_tovaru, suma, ks, stav) values ("
                        + "'" + id_usera + "', "
                        + "'" + tov + "', "
                        + "'" + suma + "', "
                        + "'" + kusy + "', "
                        + "'" + orderstatus + "')";

                String sstr2 = "delete from kosik where ID = " + zaz;

                stmt2.executeUpdate(sstr);
                stmt3.executeUpdate(sstr2);
            }

            stmt.close();
            stmt2.close();
            stmt3.close();
        } catch (Exception e) {
            out.println("Error in writing: " + e.toString());
        }

However this code always delete only one record from table "kosik". I'm trying to figure how to delete all records in this table.

Can anyone tell me what I'm doing wrong? I will be very grateful.

Thank you.

Upvotes: 1

Views: 77

Answers (4)

Vishak A Kamath
Vishak A Kamath

Reputation: 137

If you want to delete the entries retaining the table structure, using the truncate command should work.

truncate table kosik

Upvotes: 0

Virgil Ionescu
Virgil Ionescu

Reputation: 337

you limit the result from the query to one row, so you always will have only one id. the execution of the sstr2 will always delete records with first id

Upvotes: 0

EzLo
EzLo

Reputation: 14199

Remove the LIMIT 1 from your SELECT so you bring up all records and not just 1. On this line:

rs = stmt.executeQuery("select ID as idzaz, ID_pouzivatela as idpou, ID_tovaru as idtov, cena as suma, ks as kusy from kosik order by ID limit 1");

Remove the limit 1:

rs = stmt.executeQuery("select ID as idzaz, ID_pouzivatela as idpou, ID_tovaru as idtov, cena as suma, ks as kusy from kosik order by ID");

Do NOT remove the WHERE from the DELETE or you will select 1 record (stmt), insert it on the other table (stmt2) and then clear all the table before inserting all the other records in obj_zoznam (stmt3).

Upvotes: 1

Rahul
Rahul

Reputation: 77896

I'm trying to figure how to delete all records in this table.

Then remove that filter where condition from your delete statement

delete from kosik

Upvotes: 1

Related Questions