Delete query on a union (1 Viewer)

BrettH

New member
Local time
Today, 18:55
Joined
Jul 5, 2012
Messages
5
Can anyone please help me. I have paid for people to fix this but no one can do it.

I simply want to delete the rows in a table that do not have matching rows in a union.

This works and returns the 2640 rows I want deleted:

Code:
SELECT * from Shares 
  Left JOIN (
    SELECT Indices.Code
      FROM Indices
      UNION
      SELECT ETFs.Code
      FROM ETFs
      UNION SELECT GICS.Code
      FROM GICS
  ) as Allcodes
  ON Allcodes.Code = Shares.Code
WHERE Allcodes.Code Is Null

And this works (if I make a table of the results above):

Code:
DELETE *
FROM Shares
WHERE EXISTS
(
    SELECT *
    FROM junk
    WHERE junk.Code = Shares.Code
)

So can anyone tell me why this returns the entire table??

Code:
SELECT *
FROM Shares
WHERE EXISTS
(
 SELECT * from Shares 
  Left JOIN (
    SELECT Indices.Code
      FROM Indices
      UNION
      SELECT ETFs.Code
      FROM ETFs
      UNION SELECT GICS.Code
      FROM GICS
  ) as Allcodes
  ON Allcodes.Code = Shares.Code
WHERE Allcodes.Code Is not Null
)

I just want to delete 2640 rows... I will do literally anything for help.
 

sneuberg

AWF VIP
Local time
Today, 00:55
Joined
Oct 17, 2014
Messages
3,506
Let's say for example that the primary key of Shares is ShareId then I'd try something like.

Code:
DELETE *
FROM Shares 
WHERE ShareId IN
(SELECT ShareId from Shares 
  Left JOIN (
    SELECT Indices.Code
      FROM Indices
      UNION
      SELECT ETFs.Code
      FROM ETFs
      UNION SELECT GICS.Code
      FROM GICS
  ) as Allcodes
  ON Allcodes.Code = Shares.Code
WHERE Allcodes.Code Is Null);
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
it will delete all records
from Shares table because
of the Left Join you have.
meaning it will return all
records from Shares.

Explain what records need
to be included in Shares table.

Do you want to delete records
in Share table whose Code is not
in the Union Query?

Or delete from Shares table
where Code in Union Query not
in Shares table?
 

BrettH

New member
Local time
Today, 18:55
Joined
Jul 5, 2012
Messages
5
arnelgp,

I am trying to delete all records from the shares table that DO NOT appear in the Union Query.
 

BrettH

New member
Local time
Today, 18:55
Joined
Jul 5, 2012
Messages
5
Steve mate,

I do appreciate your help, but when I replace the ShareId that you have used the thing just hangs for hours. No go mate, but thanks. I am still no closer.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 03:55
Joined
Apr 27, 2015
Messages
6,286
At the risk of being a ButtInSki, BrettH, if you answer Arnelgp's questions then a solution can be given.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:55
Joined
May 7, 2009
Messages
19,169
using SQL query to delete Unmatched
records on Shares table takes too
much time and stalls the system.
you can't even see what is
happening under the hood.

using recordset also is slow on
large records. but the goodnews
is you have a chance of seeing
the progress.

save your Union Query(say qryUnion).
paste code in a Standard Module
and run it.

********************************
before running make
sure to Comment/Uncomment two
lines of code from the function
to ensure correct datatype.
********************************

Code:
Public Function deleteShares()

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim counter As Long
    Dim totalRecords As Long
    
    Set db = CurrentDb
    Set rsTarget = db.OpenRecordset("Shares", dbOpenDynaset)
    Set rsSource = db.OpenRecordset("qryUnion", dbOpenSnapshot)  '<= put the correct queryname you made
    
    With rsTarget
        .MoveLast
        totalRecords = .RecordCount
        SysCmd acSysCmdInitMeter, "Deleting Orphan Records From Shares", totalRecords
        While Not .BOF

	    '* Comment out this line if Code field is Numeric
            rsSource.FindFirst "Code = '" !Code & "'"

	    '* Uncomment this line if Code field is Numeric
	    'rsSource.FindFirst "Code = " & !Code

            If rsSource.NoMatch Then .Delete
            counter = counter + 1
            SysCmd acSysCmdUpdateMeter, counter
            DoEvents
            .MovePrevious
        Wend
    End With
    rsSource.Close
    rsTarget.Close
    Set rsSource = Nothing
    Set rsTarget = Nothing
    Set db = Nothing
    SysCmd acSysCmdRemoveMeter
End Function
 

Users who are viewing this thread

Top Bottom