4

I need to negate the value of a column for all the rows in a column. I am trying to do so using the following query, but it is not updating any Row:

sqliteDatabase.rawQuery("UPDATE "+ SQL_TABLE_NAME + " SET "+ COL_1 +" = " + "-"+COL_1  , null);

Where COL-1 = col_1, which is of type integer in the table.

--Twinks

Sarwar Erfan
  • 18,034
  • 5
  • 46
  • 57
help
  • 120
  • 1
  • 1
  • 10

2 Answers2

5

You could Multiply the column by -1

 Update SQL_TABLE_NAME Set COL_1 = COL_1 * -1

Mind you saying that, your query format works for me;

 Update tPerson Set Age = -Age

If I were you I'd create a string and set your query to that so you can check it's being set as you expect:

 String sql = "UPDATE "+ SQL_TABLE_NAME + " SET "+ COL_1 +" = " + "-" + COL_1;
 sqliteDatabase.rawQuery(sql, null); //breakpoint here, and check sql var

Does your SQL_TABLE_NAME have a space in it? Try wrapping it in square brackets, and perhaps the same with your column name.

Edit:

I would recommend updating primary keys to be negative, but to accomplish this you could do something like this:

 SET IDENTITY_INSERT Person ON

 Insert Into Person ([Id], [Name], [Age])
 Select -[Id], [Name], [Age] From Person

 SET IDENTITY_INSERT Person OFF

(but this will duplicate your table, you might want to shove the data into a temp table) - however I think this is a horrible solution. I'd try and find a way to restructure your table schema so it isn't the primary key.

firefox1986
  • 1,602
  • 11
  • 9
  • the mCount is coming out to be -1 after i execute the query. Table and column name do not have space, they have _ to make it meaningful – help Mar 24 '11 at 09:31
  • One thing I forgot to mention is that COL_1 is a primary key.. In case if that affects – help Mar 24 '11 at 09:39
  • @help I'd take a look at this: [link](http://stackoverflow.com/questions/2473215/negative-primary-keys) – firefox1986 Mar 24 '11 at 09:42
2
sqliteDatabase.rawQuery("UPDATE "+ SQL_TABLE_NAME + " SET "+ COL_1 +" = " + "-1*"+COL_1 , null);

OR

sqliteDatabase.rawQuery("UPDATE "+ SQL_TABLE_NAME + " SET "+ COL_1 +" = " + "0-"+COL_1 , null);

----EDIT1-----

sqliteDatabase.rawQuery("UPDATE "+ SQL_TABLE_NAME + " SET `"+ COL_1 +"` = " + "(`"+COL_1 +"` * -1)", null);
Sarwar Erfan
  • 18,034
  • 5
  • 46
  • 57