Subject: | Re: SQL Update Error
| Date: | Mon, 21 Aug 2017 17:26:28 +1000
| From: | Tom Krieg <aldemaar@gmail.com>
| Newsgroups: | pnews.paradox-development
|
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
>>>>
>>>>
>>
|