Deleting rows within a SQL select query

Sorry this is my first time writing a post and I have only started my coding journey, so I will probably not write this post very well. I’ve tried googling this issue but to no avail (at least not in a way I can understand it).

I’ve written a SQL SELECT query and it works, but I want to delete some rows in it before I export the data. I can only see the views of the tables and I can’t create any new tables (as in I don’t have permissions to do it and getting them is not an option).

From what I have read I can use the DELETE query, where I put my SELECT query after DELETE FROM but before the WHERE. In all the examples that I have seen people put in the name of the table they were deleting from after DELETE FROM, but as I understand I don’t have any name to put there as I deleting it from a query. I wonder if I need to adjust my SELECT code in some way for this to work.

Does anyone have any examples or suggestions how to make it work? Whenever I try, Microsoft SQL Server Management Studio seems to highlight that the problem is with my SELECT statement, but as I have noted, if I run it on itself it does the job. Microsoft SQL Server Management Studio seems to also think that my final WHERE (which should apply to the DELETE FROM) applies to the SELECT query.

The alternative is I just download my select results in clear them in R, but I am trying to learn… I am happy to post the code, but my SELECT query is a couple of pages long, so I wonder if there is any sense….

Thanks!

---

OK here is more info, my select query is about 9 unions long, each similar to the join below (sorry, slightly edited)

SELECT

A.Identifier AS 'Patient ID',
A.FTID,
A.ID,
A.Clinic_Date,
A.Completed,
A.Preferred_Language AS 'Language',
A.XYZ_Name AS 'XYZ',
A.YearOfBirth AS 'Birth Year',
A.Pathway,
A.EventType,

B.Answer_Label_Knee_Joint_Treatment_En AS 'Knee Side',
B.Answer_Label_Left_Knee_Describe_Pain_En AS 'OxfordKnee 1',
B.Answer_Label_Left_Knee_Washing_Drying_En AS 'OxfordKnee 2',
B.Answer_Label_Left_Knee_Using_Vehicle_En AS 'OxfordKnee 3',
B.Answer_Label_Left_Knee_Walking_En AS 'OxfordKnee 4',
B.Answer_Label_Left_Knee_Standing_Up_En AS 'OxfordKnee 5',
B.Answer_Label_Left_Knee_Limping_En AS 'OxfordKnee 6',
B.Answer_Label_Left_Knee_Kneeling_And_Up_En AS 'OxfordKnee 7',
B.Answer_Label_Left_Knee_Troubled_En AS 'OxfordKnee 8',
B.Answer_Label_Left_Knee_Usual_Work_En AS 'OxfordKnee 9',
B.Answer_Label_Left_Knee_Giving_Way_En AS 'OxfordKnee 10',
B.Answer_Label_Left_Knee_Household_Shopping_En AS 'OxfordKnee 11',
B.Answer_Label_Left_Knee_Walking_Down_En AS 'OxfordKnee 12',
B.Left_Score AS 'OxfordKnee Score',
'Left' AS 'Knee Label Manual'

FROM ABC AS A
INNER JOIN DEF AS B ON A.ID = B.ID

WHERE A.XYZ_Name = 'Bob' 
ORDER BY A.Clinic_Date, A.Identifier, A.Completed'

I need to delete the rows from my query where all the the values for OxfordKnee 1-12 are NULL, so as long as a row has something for one of these values I am keeping it. I’ve tried to add to AND NOT B.Answer_Label_Left_Knee_Describe_Pain_En = NULL at the end of my WHERE clause just to try this out but it returns a completely empty table…

Answer

Consider using a where of:

WHERE A.XYZ_Name = 'Bob' 
AND 
(B.Answer_Label_Left_Knee_Describe_Pain_En is NOT NULL
OR B.Answer_Label_Left_Knee_Washing_Drying_En is not null
OR B.Answer_Label_Left_Knee_Using_Vehicle_En is not null
OR B.Answer_Label_Left_Knee_Walking_En is not null,
OR B.Answer_Label_Left_Knee_Standing_Up_En is not null
OR B.Answer_Label_Left_Knee_Limping_En is not null
OR B.Answer_Label_Left_Knee_Kneeling_And_Up_En is not null
OR B.Answer_Label_Left_Knee_Troubled_En is not null
OR B.Answer_Label_Left_Knee_Usual_Work_En is not null
OR B.Answer_Label_Left_Knee_Giving_Way_En is not null
OR B.Answer_Label_Left_Knee_Household_Shopping_En is not null
OR B.Answer_Label_Left_Knee_Walking_Down_En is not null)

Alternatively use not exists and correlated subquery if you can define the set you don’t want

WHERE A.XYZ_NAME = 'Bob'
  and NOT EXISTS 
 (SELECT 1 
  FROM DEF Z
  WHERE Z.ID=B.ID  --uses correlation so say not those where all are not those where all oxford knees are null
    AND (Z.Answer_Label_Left_Knee_Describe_Pain_En is NULL
    AND Z.Answer_Label_Left_Knee_Washing_Drying_En is NULL
    AND Z.Answer_Label_Left_Knee_Using_Vehicle_En is NULL
    AND Z.Answer_Label_Left_Knee_Walking_En is NULL
    AND Z.Answer_Label_Left_Knee_Standing_Up_En is NULL
    AND Z.Answer_Label_Left_Knee_Limping_En is NULL
    AND Z.Answer_Label_Left_Knee_Kneeling_And_Up_En is NULL
    AND Z.Answer_Label_Left_Knee_Troubled_En is NULL
    AND Z.Answer_Label_Left_Knee_Usual_Work_En is NULL
    AND Z.Answer_Label_Left_Knee_Giving_Way_En is NULL
    AND Z.Answer_Label_Left_Knee_Household_Shopping_En is NULL
    AND Z.Answer_Label_Left_Knee_Walking_Down_En is NULL)