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…
Advertisement
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)