UPDATE "Contact"
SET "OfficeLandLine" = ( SELECT "CompanyPhone" FROM "CompanyDetails" WHERE "CompanyID" = 'JOHNDOEI01' )
WHERE "ContactID" = 2
UPDATE "MiscTemplate"
SET "SOUSA_BasePrice" = (SELECT "SOUSAExport"."pListPrice" FROM "SOUSAExport" JOIN "MiscTemplate" ON "SOUSAExport"."pID" = "MiscTemplate"."ClientItemNo"),
"SOUSA_MetaDesc" = (SELECT "SOUSAExport"."pLongDescription" FROM "SOUSAExport" JOIN "MiscTemplate" ON "SOUSAExport"."pID" = "MiscTemplate"."ClientItemNo"),
"SOUSA_MetaAbstract" = (SELECT "SOUSAExport"."pDescription" FROM "SOUSAExport" JOIN "MiscTemplate" ON "SOUSAExport"."pID" = "MiscTemplate"."ClientItemNo")
2: Single value expected
Sub Main
dim ods as variant
dim odb as variant
dim oConn as variant
dim oStmtLoop as variant
dim oStmtVals as variant
dim oLoopRS as variant
dim oUpdatePrepStmt as variant
dim oValuesRS as variant
ods = CreateUnoService("com.sun.star.sdb.DatabaseContext").getByName( "Update_SQL")
oConn = ods.getConnection( "", "" )
oStmtLoop = oConn.CreateStatement
oStmtVals = oConn.CreateStatement
oUpdatePrepStmt = oConn.PrepareStatement( "UPDATE ""MiscTemplate"" SET ""SOUSA_BasePrice"" = ?, ""SOUSA_MetaDesc"" = ?, ""SOUSA_MetaAbstract"" = ? WHERE ""ClientItemNo"" = ?" )
oLoopRS = oStmtLoop.executeQuery( "SELECT ""ClientItemNo"" FROM ""MiscTemplate"" " )
oLoopRS.Next ' need to move to the first record
do
oValuesRS = oStmtVals.executeQuery( "SELECT ""pListPrice"", ""pLongDescription"", ""pDescription"" FROM ""SOUSAExport"" WHERE ""pID"" = " & oLoopRS.getInt( 1 ) )
oValuesRS.Next
oUpdatePrepStmt.setDouble( 1, oValuesRS.getDouble( 1 ) )
oUpdatePrepStmt.setString( 2, oValuesRS.getString( 2 ) )
oUpdatePrepStmt.setString( 3, oValuesRS.getString( 3 ) )
oUpdatePrepStmt.setInt( 4, oLoopRS.getInt( 1 ) )
oUpdatePrepStmt.ExecuteUpdate
oLoopRS.Next
loop UNTIL oLoopRS.isAfterLast = TRUE
oConn.Dispose
End Sub
Edit: |
oConn.CreateStatement().execute("UPDATE ""tblInventory"" SET ""InvnSynced"" = 'true' " & _
"WHERE ""tblInventory"".""InventoryAutoID"" = ""tblFilter"".""fltr1"" " & _
"AND ""tblFilter"".""fltrID"" = 'filter'")
oConn.CreateStatement().execute("UPDATE ""tblInventory"" SET ""InvnSynced"" = 'true' " & _
"WHERE ""tblInventory"".""InventoryAutoID"" = (SELECT ""fltr1"" " & _
"FROM ""tblFilter"" WHERE ""tblFilter"".""fltrID"" = 'filter')")
Users browsing this forum: No registered users and 0 guests