Paradox Community

Items in pnews.paradox-development

Subject:Re: SQL Update Error
Date:Mon, 21 Aug 2017 15:28:44 -0600
From:Liz McGuire <liz@paradoxcommunity.com>
Newsgroups:pnews.paradox-development
The error indicates you get more than one record in your subquery **for 
each icn value in t1** - in other words, 1 record in t1 has many records 
in t2.  You can fix this by using FIRST() or MAX() or MIN() or LAST()...

UPDATE ':PRIV:__Sql_run148.db' t1
SET t1.'pph' = (SELECT  FIRST(t2.'PPh')
FROM ':PRIV:__sql_run147.db' t2
WHERE   t1.'icn' = t2.'icn'
)

...above is untested, check if your SQL recognizes FIRST().  Or by using 
a join, so that the tbl1 column will get set multiple times:

UPDATE `Tbl1`
FROM `Tbl1` `t1`
      JOIN `Tble2` `t2` ON `t1`.`Colx` = `t2`.`colx`
SET `Coly` = `t2`.`Coly`

...I think that's the right syntax for using a FROM clause with an 
UPDATE (am not at work and don't have any references here).

Liz


On 19 Aug 2017 18:19, 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 thedbcommunity.com