Subject: | Re: Local SQL Delete queries select statement
| Date: | Thu, 13 Feb 2020 12:57:22 +0100
| From: | Anders Jonsson <andersREMOVETHIS@jonssondata.se>
| Newsgroups: | pnews.paradox-development
|
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
>>
>
|