{"id":4153,"date":"2024-10-31T11:54:16","date_gmt":"2024-10-31T04:54:16","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4153"},"modified":"2024-10-31T16:50:59","modified_gmt":"2024-10-31T09:50:59","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-go\/delete\/","title":{"rendered":"SQLite Go: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete one or more rows from a table in Go.<\/p>\n\n\n\n

How to delete data from a table<\/h2>\n\n\n\n

To delete from a row in a table, you follow these steps:<\/p>\n\n\n\n

First, connect to the SQLite database file<\/a>:<\/p>\n\n\n

db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
sql := \"DELETE FROM table ...\"<\/span>;<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to pass data to the DELETE<\/code> statement, you can use the ?<\/code> placeholder. For example:<\/p>\n\n\n
DELETE<\/span> FROM<\/span> table<\/span>\nWHERE<\/span> id<\/span> = ?<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, call the Exec()<\/code> method of the DB<\/code> struct to execute the DELETE<\/code> statement:<\/p>\n\n\n
result, err = db.Exec(sql)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
If the DELETE<\/code> statement includes placeholders (?<\/code>), you need to pass corresponding values in the Exec()<\/code> method:<\/p>\n\n\n
result, err = db.Exec(sql, id)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
The Exec()<\/code> method returns two values: <\/p>\n\n\n\n
    \n
  • The result<\/code> is an instance of the struct that implements the Result<\/code> interface.<\/li>\n\n\n\n
  • The err <\/code> is an instance of the Error<\/code>.<\/li>\n<\/ul>\n\n\n\n
    The Result<\/code> interface includes the RowsAffected()<\/code> method that returns the number of rows deleted by the DELETE<\/code> statement.<\/p>\n\n\n\n
    Deleting a row from the countries table<\/h2>\n\n\n\n
    Step 1. Define an Delete()<\/code> function in the country.go<\/code> file, which deletes a country by an ID from the countries<\/code> table:<\/p>\n\n\n
    func<\/span> Delete<\/span>(db *sql.DB, id int<\/span>)<\/span> (int64<\/span>, error)<\/span><\/span> {\n    sql := `DELETE FROM countries WHERE id = ?`<\/span>\n    result, err := db.Exec(sql, id)\n    if<\/span> err != nil<\/span> {\n        return<\/span> 0<\/span>, err\n    }\n    return<\/span> result.RowsAffected()\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, construct a DELETE<\/code> statement that deletes a row in the countries<\/code> table:<\/p>\n\n\n
    sql := `DELETE FROM countries WHERE id = ?`<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, call the Exec()<\/code> method to execute the DELETE<\/code> statement:<\/p>\n\n\n
    result, err := db.Exec(sql, id)<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, return 0<\/code> and Error<\/code> if an error occurs during the update<\/code>:<\/p>\n\n\n
    if<\/span> err != nil<\/span> {\n   return<\/span> 0<\/span>, err\n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, return the number of rows deleted by the DELETE<\/code> statement:<\/p>\n\n\n
    return<\/span> result.RowsAffected()<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Call the Delete()<\/code> function in the main<\/code> function to delete the country id 1:<\/p>\n\n\n
    package<\/span> main\n\nimport<\/span> (\n    \"database\/sql\"<\/span>\n    \"fmt\"<\/span>\n\n    _ \"github.com\/glebarez\/go-sqlite\"<\/span>\n)\n\nfunc<\/span> main<\/span>()<\/span><\/span> {\n    \/\/ connect to the SQLite database<\/span>\n    db, err := sql.Open(\"sqlite\"<\/span>, \".\/my.db\"<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }\n    defer<\/span> db.Close()\n\n\n    \/\/ Delete the country with id 1<\/span>\n    _, err = Delete(db, 1<\/span>)\n    if<\/span> err != nil<\/span> {\n        fmt.Println(err)\n        return<\/span>\n    }    \n    \n}<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 4. Run the Go program<\/p>\n\n\n
    go<\/span> run main.go<\/span> country.go<\/span><\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Verifying data<\/h2>\n\n\n\n
    Step 1. Open your terminal and navigate to the project directory.<\/p>\n\n\n\n
    Step 2. Connect to the my.db<\/code> using the sqlite3<\/code> shell:<\/p>\n\n\n
    sqlite3 my.db<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 2. Format the query output:<\/p>\n\n\n
    .header on\n.mode column\n.nullvalue null<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Retrieve data from the countries<\/code> table:<\/p>\n\n\n
    select<\/span> * from countries where id = 1<\/span>;<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    <\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output shows an empty set indicating that the program has deleted the country with id 1<\/p>\n\n\n\n
    Step 4. Quit the sqlite3<\/code> tool:<\/p>\n\n\n
    .quit<\/code><\/span>Code language:<\/span> Go<\/span> (<\/span>go<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Call the Exec()<\/code> method of the DB<\/code> struct to execute an DELETE<\/code> statement to delete a row from a table.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Second, construct a DELETE<\/a> statement that deletes one or more rows from a table:<\/p>\n\n\n