title: SQLite Android update field from other fields
body: <p>I am trying to update an integer field in my SQLite database by combining three other integer fields but I need to also prefix two the of the other fields with a zero if the length of the field value is 1.</p>
  
  <p>This is a routine I am trying to do via the Upgrade Database method in my DBAdapter Class.</p>
  
  <p>So I need to change this current update :</p>
  
  <pre><code>if (newVersion==2) {
    populatesql = "update " + DB_TABLE + 
                  " set field1="+ COL_FIELD2 +
                  " || " +
                  COL_FIELD3 + 
                  " || " + 
                  COL_FIELD4;
  }
  </code></pre>
  
  <p>to something like this (yes I know the the prefixzero would not work as it needs String not integer but I tried it with String and made it static but it did not work)</p>
  
  <pre><code>if (newVersion==2) {
    populatesql = "update " + DB_TABLE + 
                  " set field1="+ COL_FIELD2 +
                  " || " +
                  prefixzero(COL_FIELD3) + 
                  " || " + 
                  prefixzero(COL_FIELD4);
  }
  
  public String prefixzero(String number) {
      String result = Integer.toString(number);
  
      // Log.d("PREFIX", "result starts with this = "+result);
      if (result.length() &gt;1 ) {
        // Log.d("PREFIX", "NO ZEROES HERE");
        return Integer.toString(number);
      }
  
     String zeroprefix = "";
     zeroprefix = "0"+result;
     // Log.d("PREFIX", zeroprefix);
  
     return zeroprefix ;
  }
  </code></pre>
  
  <p>The database fields are declared as follows:</p>
  
  <pre><code>public static final String COL_FIELD4 = "day";
  public static final String COL_FIELD3 = "month";
  public static final String COL_FIELD2 = "year";
  public static final String COL_FIELD1 = "long_date";
  </code></pre>
  
  <p>This is the error I get leaving out the spaces. It is as thought without the spaces the field names are simply joined together. The code used is below this error.</p>
  
  <pre><code>02-28 07:34:00.919: E/AndroidRuntime(18332): java.lang.RuntimeException: Unable to start activity ComponentInfo{co.uk.common.myapp/co.uk.common.myapp.MainActivity}: android.database.sqlite.SQLiteException: no such column: yearmonthday: update Log set long_date= yearmonthday
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1658)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1674)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread.access$1500(ActivityThread.java:117)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:942)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.os.Handler.dispatchMessage(Handler.java:99)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.os.Looper.loop(Looper.java:130)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread.main(ActivityThread.java:3735)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at java.lang.reflect.Method.invokeNative(Native Method)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at java.lang.reflect.Method.invoke(Method.java:507)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:904)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:662)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at dalvik.system.NativeStart.main(Native Method)
  02-28 07:34:00.919: E/AndroidRuntime(18332): Caused by: android.database.sqlite.SQLiteException: no such column: yearmonthday: update Log set long_date= yearmonthday
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.database.sqlite.SQLiteDatabase.native_execSQL(Native Method)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1763)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at co.uk.shieldstothemax.blastedneighbours.DBAdapter$DBHelper.onUpgrade(DBAdapter.java:68)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:132)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at co.uk.shieldstothemax.blastedneighbours.DBAdapter.open(DBAdapter.java:87)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at co.uk.shieldstothemax.blastedneighbours.MainActivity.onCreate(MainActivity.java:123)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
  02-28 07:34:00.919: E/AndroidRuntime(18332):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1622)
  </code></pre>
  
  <p>Cod in the onUpgrade method:</p>
  
  <pre><code>if (newVersion==2) {
  
                   populatesql = "update "+ DB_TABLE +" set long_date= "+getDateStr(COL_FIELD2,COL_FIELD3,COL_FIELD4);                
                  }
  </code></pre>
  
  <p>The getDateStr:</p>
  
  <pre><code>public static String getDateStr(String year, String month,String day) {
          return year+ prefixfieldzero(month)+ prefixfieldzero(day);
       }
  </code></pre>
  
  <p>the prefixfieldzero :</p>
  
  <pre><code>public static String prefixfieldzero(String number) {
              String result = String.valueOf(number);
            //  Log.d("PREFIX", "result starts with this = "+result);
              if (result.length() &gt;1 ) {
               // Log.d("PREFIX", "NO ZEROES HERE");
               return number;
               }
  
             String zerofieldprefix = "";
             zerofieldprefix = "0"+result;
            // Log.d("PREFIX", zeroprefix);
  
                return zerofieldprefix ;
          }
  </code></pre>
  
  <p>Here is my DBAdapter :</p>
  
  <pre><code>import android.content.ContentValues;
  import android.content.Context;
  import android.database.Cursor;
  import android.database.sqlite.SQLiteDatabase;
  import android.database.sqlite.SQLiteOpenHelper;
  import android.util.Log;
  
  
  public class DBAdapter {
      private static final String DB_NAME = "TABLE";
      private static final String DB_TABLE = "Log";
      private static final int DB_VERSION = 2;
  
      private static final String DB_CREATE = "CREATE TABLE IF NOT EXISTS "+DB_TABLE+ 
                  " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                         "type_id INTEGER, " +" hour INTEGER, " + 
                           "min INTEGER , " +" info VARCHAR,"+"image_desc VARCHAR, "+ 
                         "day INTEGER, "+"month INTEGER,  "+ "year INTEGER,"+" second INTEGER,long_date INTEGER ,date_time_long INTEGER NOT NULL "+");" ;
  
  
          public static final String COL_TYPE = "type_id";
          public static final String COL_HOUR = "hour";
          public static final String COL_MIN = "min";
          public static final String COL_SEC = "second";
          public static final String COL_IMAGE = "image_desc";
  
          public static final String COL_DAY = "day";
          public static final String COL_MON = "month";
          public static final String COL_YEAR = "year";
          public static final String COL_DATE = "date";
          public static final String COL_LONG_DATE = "long_date";
          public static final String COL_INFO = "info";   
          public static final String COL_ID = "_id";
  
          private SQLiteDatabase mDB;
          private DBHelper mDBHelper;
          private Context mCtx;
  
          private static class DBHelper extends SQLiteOpenHelper{
              public DBHelper(Context context) {
                  super(context, DB_NAME, null, DB_VERSION);
              }
  
              @Override
              public void onCreate(SQLiteDatabase db) {
                  db.execSQL(DB_CREATE);
  
              }
  
              @Override
              public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                  Log.v("OLDDBVERSION", "Old version="+String.valueOf(oldVersion));
                  Log.v("NEWDBVERSION", "New version="+String.valueOf(newVersion));
                  //String upgradesql = null;
                  String populatesql = null;
                  if (newVersion==2) {
  
                   populatesql = "update "+ DB_TABLE +" set long_date= "+getDateStr(COL_YEAR,COL_MON,COL_DAY);                
                  }
                  if (populatesql !=null) {
                      //db.execSQL(upgradesql);               
                      if (populatesql !=null) {
  
                          db.execSQL(populatesql);
                      }
                  }
  
  
  
          } 
     }
  
          public DBAdapter(Context ctx)
          {
              this.mCtx = ctx;
          }
  
          //OPEN METHOD AND CLOSE METHOD
          public DBAdapter open()
          {
              mDBHelper = new DBHelper(mCtx);
              mDB = mDBHelper.getWritableDatabase(); //important
              return this;
          }
  
          public void close()
          {
              mDBHelper.close();
          }
  
          //INSERT AND DELETE
          public long createLog(String info,String image, Integer type,Integer hour,Integer mins,Integer sec,Integer day,Integer month,Integer year,Long longdate,Long date)
          {
              ContentValues v = new ContentValues();
              v.put(COL_TYPE, type);
              v.put(COL_INFO, info);
              v.put(COL_IMAGE, image);
              v.put(COL_HOUR, hour);
              v.put(COL_SEC, sec);
              v.put(COL_MIN, mins);
              v.put(COL_DAY, day);
              v.put(COL_MON, month);
              v.put(COL_YEAR, year);
              v.put(COL_DATE, date);
              v.put(COL_LONG_DATE, longdate);
  
              return mDB.insert(DB_TABLE, null, v);
          }
  
          public boolean deleteLogRec(long id)
          {
              return mDB.delete(DB_TABLE, COL_ID + "="+ id, null)&gt;0;
          }
  
          public boolean deleteAllLogs()
          {
              return mDB.delete(DB_TABLE, null, null)&gt;0;
          }
  
          //get all bookmark
          public Cursor GetAllLogs(Integer i,String sortfield)
          {
              String sorted = "";
              if (i == 1 ) {
                  sorted = "DESC";
              } else if (i == 2) {
                  sorted = "ASC";
              } 
  
  
              return mDB.query(DB_TABLE, new String[] {COL_ID, COL_TYPE,COL_IMAGE, COL_INFO,COL_IMAGE,COL_HOUR,COL_SEC,COL_MIN,COL_DAY,COL_MON,COL_YEAR,COL_SORT_DATE},
                              null, null, null, null, COL_DATE+" "+sorted);
          }
  
          public Cursor allrecords() {
  
              return mDB.query(DB_TABLE, new String[] {COL_ID, COL_TYPE,COL_IMAGE, COL_INFO,COL_IMAGE,COL_HOUR,COL_SEC,COL_MIN,COL_DAY,COL_MON,COL_YEAR,COL_SORT_DATE},
                      null, null, null, null, null);
          }
  
  
  
  
  
          //get a specific log by id
          public Cursor getLog(long id)
          {
              Cursor mCursor = mDB.query(true, DB_TABLE, new String[] {COL_ID, COL_TYPE,COL_IMAGE, COL_INFO,COL_IMAGE,COL_HOUR,COL_SEC,COL_MIN,COL_DAY,COL_MON,COL_YEAR},
                      COL_ID + "=" + id,
                      null, null, null, null, null);
  
              if (mCursor!=null)
                  mCursor.moveToFirst();
              return mCursor;
          }
  
          //finally update the log
          public boolean updateLog(long id, String uinfo, String uimage,Integer utype,Integer uhour,Integer usec,Integer umin,Integer uday,Integer umonth,Integer uyear,Long ulongdate,Long udate)
          {
              ContentValues v = new ContentValues();
              v.put(COL_TYPE, utype);
              v.put(COL_INFO, uinfo);
              v.put(COL_IMAGE, uimage);
              v.put(COL_HOUR, uhour);
              v.put(COL_SEC, usec);
              v.put(COL_MIN, umin);
              v.put(COL_DAY, uday);
              v.put(COL_MON, umonth);
              v.put(COL_YEAR, uyear);
              v.put(COL_DATE, udate);
              v.put(COL_SORT_DATE, ulongdate);
  
              return mDB.update(DB_TABLE, v, COL_ID + "=" + id, null) &gt; 0;
          }   
  
          public static String prefixfieldzero(String number) {
              String result = String.valueOf(number);
            //  Log.d("PREFIX", "result starts with this = "+result);
              if (result.length() &gt;1 ) {
               // Log.d("PREFIX", "NO ZEROES HERE");
               return number;
               }
  
             String zerofieldprefix = "";
             zerofieldprefix = "0"+result;
            // Log.d("PREFIX", zeroprefix);
  
                return zerofieldprefix ;
          }
  
      public static String getDateStr(String year, String month,String day) {
              return year+ prefixfieldzero(month)+ prefixfieldzero(day);
           }
  
  
  }
  </code></pre>
  
  <p>Tried to run the SQL as per your latest answer but get this error :</p>
  
  <pre><code>03-01 00:20:05.379: E/AndroidRuntime(19060): Caused by: android.database.sqlite.SQLiteException: near "AS": syntax error: update TABLE AS TABLE_Temp1 set long_date = (select year || substr('00' || month ,-2,2  ) || substr('00' || ,day, -2 ,2)  from Log AS TABLE_Temp2 where TABLE_Temp1.id=TABLE_Temp2.id   
  </code></pre>
  
  <p>Current SQL used to update long date to the combination of the year month and day:</p>
  
  <pre><code>populatesql = "update "+DB_TABLE + " AS LogTemp1 set long_date = (select year || substr('00' || month ,-2,2  ) || substr('00' || ,day, -2 ,2)  from "+DB_TABLE+" AS LogTemp2 where LogTemp1.id=LogTemp2.id   "  ; 
  </code></pre>
  
  <p>I have also tried it as follows:</p>
  
  <pre><code>populatesql = "update "+DB_TABLE + " AS LogTemp1 set long_date = (select year || substr('00' || month ,-2,2  ) || substr('00' || ,day, -2 ,2)  from "+DB_TABLE+" AS LogTemp2 where **LogTemp1._id=LogTemp2._id**   "  ; 
  </code></pre>