Thanks!

On 2/13/2020 5:57 AM, Anders Jonsson wrote:
> I think you missed the point, if you are using "=" the subquery must 
> return only ONE record. Using DISTINCT doesn't give you that.
> 
> If you use "IN", the sub query can return multiple records.
> 
> You can do:
> 
> DELETE
> FROM
> ":WORK:Artiklar.DB"
> WHERE
> IDnr in
> (
> SELECT DISTINCT IDnr
> FROM ":PRIV:ToDelete.DB"
> )
> 
> 
> You could also use this:
> 
> DELETE
> FROM
> ":WORK:Artiklar.DB"
> WHERE
> IDnr =
> (
> SELECT  IDnr
> FROM ":PRIV:ToDelete.DB"
> where
> Name = 'Apple'
> )
> 
> But this will fail if there is more than one item with the Name "Apple" 
> in the sub-table. Using DISTINCT will not help as it will only make sure 
> that the column IDnr doesn't return dublicates, but it could still 
> return more than one record.
> 
> 
> An example where "=" will work
> 
> DELETE
> FROM
> ":WORK:Artiklar.DB"
> WHERE
> IDnr =
> (
> SELECT  MAX(IDnr)
> FROM ":PRIV:ToDelete.DB"
> )
> 
> Anders
> 
> 
> 
> Den 2020-02-12 kl. 15:31, skrev Mark Bannister:
>> Doesn't work with DISTINCT either.
>>
>> On 2/11/2020 7:09 PM, Tom Krieg wrote:
>>> I don't know much about local SQL but it won't work because the 
>>> sub-query could return more than one result.
>>>
>>> try WHERE ID IN (SELECT id5 ... etc
>>>
>>> On 12/02/2020 5:21 am, Mark Bannister wrote:
>>>> Do local sql delete queries not work at all when using a select 
>>>> sub-query?
>>>> DELETE FROM  ':PRIV:I0IPDIMXREF2.DB'
>>>>
>>>> WHERE (id =
>>>> (SELECT id5
>>>> FROM ':PRIV:__Sql_run68.db' )
>>>> )
>>>>
>>>> Reports it works.  Doesn't delete anything.
>>>> -- 
>>>> Mark B
>>>
>>
>