Can't get this SQL to work :(

Databases
1

I have a table named “GEOMETRY” which looks like this

Create Table geometry ( 
   keyValue Integer,
   control_id Integer,
   PropertyName text Collate Nocase,
  PropertyValue text)

propertyName field has one of the following values “LEFT”,“TOP”,“WIDTH”,“HEIGHT” …
propertyVALUE is well, the value of the property (it is TEXT as there are other related tables with similar structure that do not have numeric values)

What I need to do is to take all those values for a given keyValue
multiply it by a constant value, and update the values for another keyValue

The SQL I created (yeah, not a prepared statement)… works, generates no errors, but sets the destination values to NULL

SQL= _
"UPDATE geometry "+_
"   SET propertyvalue=("+_
"   SELECT ROUND(CAST(propertyValue AS Double)*"+Str(hmult)+",1)"+_
"     FROM geometry b"+_
"    WHERE b.keyValue   = "+Str(from_keyValue) + _
"      AND b.control_id    = "+Str(ctrl_ID)+ _
"      AND b.propertyname in('left','width')" +_
")"+_
"WHERE keyValue   = "+Str(to_keyValue)+ _
"  AND control_id  = "+Str(ctrl_ID)+ _
"  AND propertyname in('left','width')"

Ctrl_ID is which single control is to be updated
From_keyValue is the key pointing to the BASELINE coordinate system
To_keyValue is the key pointing to the record to be updated
hmult is a value that was precalcuated

I know that Geometry is correct BEFORE this SQL runs
If I execute just the subquery, it returns the correct values
hMult is a valid value, as a matter of fact if I examine the generated SQL it looks just fine
It does not create an exception (it when I do the DB_EXECUTE, no error is generated)
HOWEVER, all the records that should be affected now have NULL in propertyValue

There is another query, where the ONLY difference is vMult instead of hMult
and “top”,“width” instead of “left”,“width”

Does anyone see some amiss? I have been beating my head on this for most of the day

2

Is it the missing whitespace before the 2nd where?

3

propertyValue is text, but looks like your subquery produces a number.

4

Wait…you’re using SQLite, correct? Number to Text wouldn’t be the problem.

But, it looks like your subquery possibly produces multiple values (records) because of the b.propertyname in(‘left’,‘width’). Don’t know if that would do it.

5

technically yes, but the previous char is “)” so it should be fine, like I said, the query DOES run without errors

[quote=364401:@Jay Madren]Wait…you’re using SQLite, correct? Number to Text wouldn’t be the problem.
But, it looks like your subquery possibly produces multiple values (records) because of the b.propertyname in(‘left’,‘width’). Don’t know if that would do it.[/quote]
yes it is SQLite… and yes it updates TWO records… but in one respect you are correct
the subquery should say

 AND b.propertyname=geometry.propertyname

but as is I would expect it to update, just perhaps with the incorrect value, but still either the LEFT or WIDTH

6

Have you tried copying the full query after it’s generated (msgbox or a breakpoint in the debugger) to a SQL editor to test? There’s got to be something funky going on with your data.

7

I just did… and guess what…
There it worked :frowning:
So why would it work running in my SQLite manager, but NOT via Xojo?

FYI… I altered the app to write the SQL query to a text file, just before Xojo executed it… so I was ensured to have the EXACT same query… frustrating

Query was also updated to this

SQL= _
"UPDATE geometry "+_
"   SET propertyvalue=("+_
"   SELECT ROUND(CAST(propertyValue AS Double)*"+Str(hmult)+",1)"+_
"     FROM geometry b"+_
"    WHERE b.keyValue   = "+Str(from_keyValue) + _
"      AND b.control_id    = "+Str(ctrl_ID)+ _
"      AND b.propertyname = geometry.propertyname"+_
")"+_
"WHERE keyValue   = "+Str(to_keyValue)+ _
"  AND control_id  = "+Str(ctrl_ID)+ _
"  AND propertyname in('left','width')"
8

Does this simpler version with a constant multiplier work on a single propertyvalue?
Ive wrapped string with quotes.
And added a WHERE …IN at the end.
Without that, if the from_keyvalue is incorrect, all records could get updated with null.

[quote]SQL= _
"UPDATE geometry “+_
" SET propertyvalue=(”+_
" SELECT CAST(propertyValue AS Double) * 1.5 " +_
" FROM geometry b “+_
" WHERE b.keyValue = '” + Str(from_keyValue) + "’ “_
" AND b.control_id = '”+Str(ctrl_ID) + "’ " _
" AND b.propertyname = ‘left’ " +_
“)”+_
“WHERE keyValue = '”+Str(to_keyValue) + “’ " _
" AND control_id = '”+Str(ctrl_ID) + “’ " _
" AND propertyname = ‘left’”

and “’” + Str(from_keyValue) + “’ in (select keyvalue from geometry)”
[/quote]

9

Ok… while it makes the query more complex, I see and understand the extra query you added…
HOWEVER… now the results are the opposite… its not updating anything

actually that is not entirely true.
the update is executed 7 times with 7 different TO_KEYVALUES…
only the last one is taking effect…

and YES, there is an DB_EXECUTE after each one

it is NOT updating except the last one…
and I added this after each execute

CALL DB_PROJECT.DB_Select("SELECT DISTINCT changes() AS xyz from geometry",rs)
debug "CHANGES = "+rs.field("xyz").StringValue
debug db_project.ErrorMessage

debug writes to a text file
each exeution says ZERO except the last one… which says 1
and I KNOW the values are there… I’m just missing something.
upper/lower case won’t matter as there is COLLATE NOCASE on the table
there are no extra spaces between the quotes

SQL= _
"UPDATE geometry "+_
" SET propertyvalue=("+_
" SELECT CAST(propertyValue AS Double) * 1.5 " +_
" FROM geometry b "+_
" WHERE b.keyValue = '" + Str(from_keyValue) + "' "_
" AND b.control_id = '"+Str(ctrl_ID) + "' " _
" AND b.propertyname = 'left' " +_
")"+_
"WHERE keyValue = '"+Str(to_keyValue) + "' " _
" AND control_id = '"+Str(ctrl_ID) + "' " _
" AND propertyname = 'left'"
" and EXISTS ("+_
" SELECT *" +_
" FROM geometry c "+_
" WHERE c.keyValue = '" + Str(from_keyValue) + "' "_
" AND c.control_id = '"+Str(ctrl_ID) + "' " _
" AND c.propertyname = 'left' )"

and I opt’d for EXISTS instead of IN, but still

10

I don’t know what values you’re using, but I replicated your table and code from the original post and it runs fine in Xojo 2017R3 on Mac. Well, runs as designed - probably incorrect but it doesn’t set any fields to null.

If you want to give me some values, I can test with them. Both existing values in the table and values for the query.

11

I broke the process into multple steps… not as efficient from an SQL point of view, but it isn’t that much slower, and it works

thanks to everyone