How to change element value in SQLite?

Question

Suppose I have this sqlite database structure:

ID PRODUCT_NAME AVAILABILITY 1 foo 0 2 bar 1 3 baz 0 4 faz 1 

How can I change the value of AVAILABILITY fom 1 → 0 where PRODUCT_NAME = 'bar' ? Something like this, Pseudocode:

 db.execSQL( "UPDATE TABLE" + Table_name + "MODIFY" + availability + "=" + 0 + "WHERE" + product_name + "like ? " + 'bar'); 

I assume that I also need to drop and recreate the table using the onCreate () and onUpgrade () methods, right? Some code would be much appreciated.

Answer 1

Use this:

 SQLiteDatabase db=dbHelper.getWritableDatabase(); String sql="update "+Table_name+" set availability='0' where product_name like 'bar'"; Object[] bindArgs={"bar"}; try{ db.execSQL(sql, bindArgs); return true; }catch(SQLException ex){ Log.d(tag,"update data failure"); return false; } 

Answer 2

You can also use the update() , insert() , query() , delete() methods that Android gives you

 // define the new value you want ContentValues newValues = new ContentValues(); newValues.put("AVAILABILITY", 0); // you can .put() even more here if you want to update more than 1 row // define the WHERE clause w/o the WHERE and replace variables by ? // Note: there are no ' ' around ? - they are added automatically String whereClause = "PRODUCT_NAME == ?"; // now define what those ? should be String[] whereArgs = new String[] { // in order the ? appear "bar" }; int amountOfUpdatedColumns = db.update("YourTableName", newValues, whereClause, whereArgs); 

The advantage is that you get the correct SQL syntax for free. It also eludes your variables that prevent bad things from "hax ' DROP TABLE '" when you use "hax ' DROP TABLE '" as an argument to ? .

The only thing that is not safe yet is to use column LIKE ? with arguments like "hello%world_" because % (matches any of several characters) and _ (matches any 1 char) is not escaped. You will need to avoid them manually (e.g. put a ! front of each _ or % ) and use

 String whereClause = "LIKE ? ESCAPE '!'" String[] whereArgs = new String[] { likeEscape("bar") // likeEscape could be replaceAll("!", "!!").replaceAll("%", "!%").replaceAll("_", "!_") maybe } 

Btw: your only line of code should work if you use

 db.execSQL( "UPDATE " + Table_name + " SET " + availability + "=0 WHERE " + product_name + " like 'bar'"); 

Answer 3

You want update not alter . alter for the database schema, update is for the data stored in it.

For example: update TABLE_NAME set AVAILABILITY = 0 where PRODUCT_NAME like 'bar';

Also, don't just string the strings together to build a sql query. Use a prepared statement or other statement generation library to avoid SQL injection attacks and errors.

Answer 4

SqlLite uses "SQL". You need SQL update "

 db.execSQL( "update mytable set availability=0 where product_name like '%" + bar + "%'"); 

Here's a good SQL reference for "select", "update", "insert" and "delete" ( "CRUD" ):

http://www.w3schools.com/sql/default.asp