Update sql command using two tables fails (hsqldb)

[timrichardson]

I want to run this SQL command

update "transactions" set "expense" = "matching"."itemcategory" from "matching" where "matching"."match_string" = 'test'

I execute it from tools-> SQL (in OOO 2.4 and 2.4.1)

I get this error:

Column not found: matching.itemcategory

I can get update SQL commands to work as long as the SET expression does not refer to a column from another table.
SQL can do this, but perhaps not HSQLDB?

"matching"."match_string" is not part of any index.

[DrewJensen]

I have two tables
Contact
CompanyDetails

I want to update the CompanyLandLine field in the Contact table for the record with the ContactID value of 2 with the value from the field CompanyPhone in CompanyDetails with the CompanyID value of JOHNDOEI01

I would use this query:

Code: Select all   Expand viewCollapse view

UPDATE "Contact"
SET "OfficeLandLine" = ( SELECT "CompanyPhone" FROM "CompanyDetails" WHERE "CompanyID" = 'JOHNDOEI01' )
WHERE "ContactID" = 2

[effjay]

This post was quite helpful to me as I'm a former Access user trying to navigate through Base.

I tried this SQL with a join in order to do a mass update based on fields from two tables matching and I get the error that it is looking for a single value. Is there any way to get around this (Please note I'm also a little rusty with my SQL so if the issue is simply syntax I apologize in advance!)? Please see my code and the error below.

Code: Select all   Expand viewCollapse view

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

[DrewJensen]

It looks right - but given the error by first place to look is if any of those select statements is returning more then one record?

To test it I would just copy / paste each into a Query Definition window, in SQL view, and run it. If any of them return 2 or more records, well there you go.

[effjay]

Yes it would be returning more than one record. So I guess that answers my question about mass update statements, at least done this way. I'll have to see if I can find another way around this. Thanks for the input!!

[DrewJensen]

hmm- maybe you misunderstood me.

You can update multiple records in the UPDDATE table, but each supplied value must be a singleton.

So I can say
UPDATE Tabl1
set "Col2" = 'Hello'

Now every record in the table holds 'Hell' in the column Col2.

UPDATE Tabl1
set "Col2" = ('Hello', 'GoodBy')

Is an error.

[effjay]

I think I get what you are saying. My logic in the statement I posted is faulty for this approach in that I have several records where ClientItemNo would be equal to pID.

Here is my exact issue, I have 34 records in SOUSAExport that have data that needs to populated in MiscTemplate. I have several tables with this issue, so rather than hunt and peck to figure out which id's match I'd much rather run it through a SQL statement to update automatically. I have tried adding a Select statement into the WHERE clause of the Update statement and received the error that it can not find SOUSAExport.pID. I get the same error (that it's not able to find SOUSAExport.pID) when I try just adding a straight WHERE MiscTemplate.ClientItemNo = SOUSAExport.pID. I double checked my spelling and made sure there were no hidden spaces in the field name.

I'm thinking I just really need to shake the rust off my SQL and dig deeper into this, I'm sure there's some logic out there that will work for this issue. I know I've done mass updates of this nature in MS SQL, it's just been a really long time.

[DrewJensen]

ok I get it now - I think...I should of read the SQL statement more closely and I would have seen it right off, sorry.

Assuming I'm on the same page now then - Nope you can 't do that - that way - in HSQLdb. ( which is the engine used in an embedded Base database )

Here is what I see you wanting then

FOR EACH ROW in MiscTemplate
SET SOUSA_BasePrice = SELECT "SOUSAExport"."pListPrice" FROM "SOUSAExport" JOIN "MiscTemplate" ON "SOUSAExport"."pID" = "MiscTemplate"."ClientItemNo"

If HSQLdb had a nice scripting language, like PL/SQL, then we could do that easily in a Stored procedure. ( BTW - that is part of the next HSQLdb release, but it will be a while before OO.o supports that new version - and it doesn't help for today does it )

So in OO.o you could do this in a Basic script. The script is not that much to write, a loop, 1 Statement and 1 PreparedStatement is all it needs.

Before I take the time to write an example maybe I should sure that I have what you want now?

[effjay]

That is exactly what I'm looking for!

[DrewJensen]

OK - the first thing I did then is to create a couple of tables in a Base file named (and registered as ) Update_SQL:

Now off to write that script that will move those three columns from the one table into the other.

As always it seems I struggle with where to start on this...you said you came over form MS Access, so I assume you don't have a handle on 'the ways' of OO.o basic yet..

Currently in OO.o there is no way to embed a Basic library ( a holder for the sub procedures ) directly in a Base file. You can embed one in a Form, that is embedded in a Base file. This was to have changed with the coming 3.0 release of OO.o, but as it appears now that is not going to happen, instead it will most likely be pushed back to 3.1.. Along with that change was to have come a new pseudo variable ThisDataBaseDocument, but that will also push back.

So - we are left with having to answer two questions: Where do I put the basic script and how do I find the database?

[effjay]

Ok, now I art confused.

Is there no way to use the Macro functionality in Base to run the basic script? Also, I did a little basic coding ages ago, is there a tutorial somewhere where I can figure out how to code something in Base myself. Though I definitely appreciate the offer of you writing something up for me and I definitely have to admit this is sounding much more complicated than what I was hoping for...

[DrewJensen]

LOL - really it is easy...I am just trying to write this, interwoven into other activities..sorry it is rather disjounted is all..I should of waited and just finished it off line and put the whole thing up as one go...

[DrewJensen]

Well - I am sorry I don't have more time...but I will make some tonight to explain this...but here is the procedure..which could be written better

Code: Select all   Expand viewCollapse view

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


And here are the tables after it runs.

Edit:

Assuming your database is registered ( Tools > Options > OpenOfice.org Base > Database ) just replace the string "Update_SQL" with your registered database name and it should actually work right on your tables. ( only other assumption is the the first field in the update is of type double...took a guess on that from the name )

[effjay]

Sorry it took me so long to respond. I'm afraid I went ahead and begged for an MS Access product key. While there are definitely some features that I like in Base, there are some features of this tool that I really don't. I'll probably continue playing around with the tool at home, but for what I'm going to be doing on a regular basis I just don't think Base is what I need.

I can't speak for the guy who started the thread, but from my standpoint I am ok with this thread being marked as resolved as I'm sure the script you provided would work if I had the patience to work with it on my current project. Thanks so much for your help with my issue!!

[lnyork6]

Sorry for reviving a 6 year old post but i think this is the same problem I'm having (or very similar).
my SQL statement is as follows (assuming "oConn" is a valid DB connection):

Code: Select all   Expand viewCollapse view

   oConn.CreateStatement().execute("UPDATE ""tblInventory"" SET ""InvnSynced"" = 'true' " & _
                  "WHERE ""tblInventory"".""InventoryAutoID"" = ""tblFilter"".""fltr1"" " & _
                  "AND ""tblFilter"".""fltrID"" = 'filter'")


Error message: "user lacks privilege or object not found: tblFilter.fltr1"
I have an other, more complicated, statement that is giving me the same issue but if i figure this one out I will know what to do with the other one.

[lnyork6]

I figured out my problem for the above statement...

Code: Select all   Expand viewCollapse view

   oConn.CreateStatement().execute("UPDATE ""tblInventory"" SET ""InvnSynced"" = 'true' " & _
                  "WHERE ""tblInventory"".""InventoryAutoID"" = (SELECT ""fltr1"" " & _
                  "FROM ""tblFilter"" WHERE ""tblFilter"".""fltrID"" = 'filter')")


with this I think I can fix the other statement I am having problems with as well