Paradox Community

Items in pnews.paradox-development

Subject:Re: SQL Update Error
Date:Mon, 21 Aug 2017 17:26:28 +1000
From:Tom Krieg <>
In SQL, your database is defined as the alias, so you would open the 
database "priv" separately and then use the database variable when 
executing your sql.

Then your SQL statements would use the tablename. You also need to 
escape any quotes used for field names.  i.e.

UPDATE __Sql_run148.db t1
SET t1.\"pph\" =  (SELECT t2.\"PPh\"
FROM __sql_run147.db t2
WHERE   t2.\"icn\" = t1.\"icn\"

Tom Krieg wrote:
> I suspect the problem is your use of a colon (:) in ':PRIV:' and perhaps
> the use of a period ('Tabl1'.'colx'). I've had generic type errors arise
> when I used certain characters in a SQL string.
> I had a post on it some time ago. I had to revert to procedures if I
> wanted to use certain characters in my SQL statements.
> Mark Bannister wrote:
>> Throws an error if I try using a join.
>> "Invalid use of keyword.
>> Line Number: 2
>> Token: JOIN"
>> On 8/19/2017 7:19 PM, Bernie van't Hof wrote:
>>> Consider using a join. Something like (for MySQL, others slightly
>>> different)
>>> UPDATE `Tbl1` `t1`
>>> JOIN `Tble2` `t2`
>>> ON `Tbl1`.`Colx` = `t2`.`colx`
>>> SET `t1`.`Coly` = `t2`.`Coly`
>>> On 20/8/17 6:14 am, Mark Bannister wrote:
>>>> Jeez, I'm terrible at sql.
>>>> I just want to update a table with values from another.
>>>> UPDATE ':PRIV:__Sql_run148.db' t1
>>>> SET t1.'pph' = (SELECT t2.'PPh'
>>>> FROM ':PRIV:__sql_run147.db' t2
>>>> WHERE t1.'icn' = t2.'icn'
>>>> )
>>>> But I get error "Single row subquery produced more than one row."
>>>> Obviously I'm going about this the wrong way. I want multiple rows.
>>>> I've got more than one row to update. I actually need to update two
>>>> fields but I can't even get past this error.
>>>> --
>>>> Mark Bannister

Copyright © 2004