Android Question how to use innerjoin of tables

[Makumbi]

Please iam trying to use innerjoin but gettting syntax error when i try to execute this

Starter.SQL1.ExecNonQuery2("UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount =quot.Get("balance")"

 

[DonManfred]

Why you need a inner join when you just want to update Balances?

 

[peacemaker]

1) https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/
or
2)

Starter.SQL1.ExecNonQuery2("UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount = '" & quot.Get("balance") & "'"

 

[Makumbi]

1) https://www.b4x.com/android/forum/threads/b4x-smart-string-literal.50135/
or
2)

Starter.SQL1.ExecNonQuery2("UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount = '" & quot.Get("balance") & "'"

iam now getting error invalid parentheses

 

[Mahares]

iam now getting error invalid parentheses

Try this:

Starter.SQL1.ExecNonQuery2("UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount = ?", _
    Array As Object( quot.Get("balance")) )

Your posts are sometimes hard to decipher because it is hard to tell what some of your variables are. If you give more details, you get better help.

 

[Makumbi]

Thank You brother i try to improve on them

 

[Makumbi]

after executing

Starter.SQL1.ExecNonQuery2("UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount = ?", _
    Array As Object( quot.Get("balance")) )

iam now getting this error

*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
ExecuteHtml: SELECT Names,Class || Stream As CS,Amount As Balance FROM Balances
** Activity (main) Pause, UserClosed = false **
** Activity (linkupdates) Create, isFirst = true **
** Activity (linkupdates) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
(JSONTokener)  at character 0 of [{"Account":"05-07152","Name":"MUTESASIRA FRANK","Stream":"W","Category":"Active","Country":"S2","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545166800000)\/"},{"Account":"05-05586","Name":"NAMIGADDE DEBORAH","Stream":"A","Category":"Active","Country":"S6","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545080400000)\/"}]
Account: 05-07152
Name: MUTESASIRA FRANK
Category: Active
Froms: /Date(1533070800000)/
Tos: /Date(1545166800000)/
08/01/2018
01/08/2018
19/12/2018
19/12/2018
[{"Account":"05-07152","Name":"MUTESASIRA FRANK","Stream":"W","Category":"Active","Country":"S2","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545166800000)\/"},{"Account":"05-05586","Name":"NAMIGADDE DEBORAH","Stream":"A","Category":"Active","Country":"S6","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545080400000)\/"}]
Account: 05-05586
Name: NAMIGADDE DEBORAH
Category: Active
Froms: /Date(1533070800000)/
Tos: /Date(1545080400000)/
01/08/2018
01/08/2018
18/12/2018
18/12/2018
[{"Account":"05-07152","Name":"MUTESASIRA FRANK","Stream":"W","Category":"Active","Country":"S2","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545166800000)\/"},{"Account":"05-05586","Name":"NAMIGADDE DEBORAH","Stream":"A","Category":"Active","Country":"S6","Froms":"\/Date(1533070800000)\/","Tos":"\/Date(1545080400000)\/"}]
(JSONTokener)  at character 0 of [{"Account":"05-07152","Name":"MUTESASIRA FRANK","Stream":"W","Class":"S2"},{"Account":"05-05586","Name":"NAMIGADDE DEBORAH","Stream":"A","Class":"S6"}]
Account: 05-07152
Name: MUTESASIRA FRANK
Class: S2
Stream: W
Account: 05-05586
Name: NAMIGADDE DEBORAH
Class: S6
Stream: A
(JSONTokener)  at character 0 of [{"Account":"05-07152","balance":373000}]
Account: 05-07152
Amount: 373000
Error occurred on line: 291 (Linkupdates)
android.database.sqlite.SQLiteException: near "INNER": syntax error (code 1): , while compiling: UPDATE Balances INNER JOIN Allacounts ON Balances.Account = Allacounts.Account SET Balances.Amount = ?
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
    at android.database.sqlite.SQLiteDatabase.compileStatement(SQLiteDatabase.java:996)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery2(SQL.java:85)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:778)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:358)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:171)
    at anywheresoftware.b4a.shell.DebugResumableSub$RemoteResumableSub.resume(DebugResumableSub.java:22)
    at anywheresoftware.b4a.BA.checkAndRunWaitForEvent(BA.java:245)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:137)
    at anywheresoftware.b4a.BA$2.run(BA.java:365)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6077)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:866)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:756)
** Service (starter) Destroy (ignored)**
** Activity (linkupdates) Pause, UserClosed = true **

 

[Mahares]

iam now getting this error

I do not think you can use INNER JOIN in SQLite UPDATE syntax. I have used it in Access and it works, but not in SQLite. You may want to have a small project that reproduces the issue and post it in the forum. 'File', 'Export as zip'. I am pretty sure someone with knowledge can help you. Also, sometimes it is dangerous to issue an UPDATE of a table without a WHERE clause.

 

[Mahares]

You may want to try something like this, but your best bet in this case is upload a small project if this does not help you:

Starter.SQL1.ExecNonQuery2("UPDATE Balances  SET Amount = ?  WHERE " _
    & "Account IN (SELECT Account FROM Allacounts)", _  
    Array As Object( quot.Get("balance")) )

 

[Erel]

You may want to try something like this, but your best bet in this case is upload a small project if this does not help you:

Starter.SQL1.ExecNonQuery2("UPDATE Balances  SET Amount = ?  WHERE " _
    & "Account IN (SELECT Account FROM Allacounts)", _ 
    Array As Object( quot.Get("balance")) )

Starter.SQL1.ExecNonQuery2($"UPDATE Balances  SET Amount = ?  WHERE 
  ccount IN (SELECT Account FROM Allacounts)"$, _
   Array( quot.Get("balance")) )