February 4, 2008 at 10:44 pm
Below arw two table Work Order Status and Work Order Images..
Fields are ID-INT, Type-VARCHAR, Status-BIT.
ID Type Status --> WO_Status
------------------
1 M True
1 P NULL
2 P False
2 M NULL
ID Type Image --> WO_Images
------------------
1 M BinaryData
1 P BinaryData
2 P BinaryData
2 M BinaryData
I need to delete from WO_Images those records whose ID and Type in WO_Status has status = NULL.
That is... ID 1, Type P and ID 2, Type M should get deleted from WO_Images.
My query was:
SELECT * FROM WO_Images
WHERE Wo_id IN
(
SELECT Wo_id
FROM WO_status
WHERE ISNULL(CONVERT(NVARCHAR,Status),'')=''
)
AND [type] IN
(
SELECT [type]
FROM WO_status
WHERE ISNULL(CONVERT(NVARCHAR,Status),'')=''
)
But it obviously doesn't work.
Can anyone help?
February 4, 2008 at 11:50 pm
By doesn't work, do you mean it gives an error, or doesn't get the rows you expect?
Try the following...
SELECT * FROM WO_Image WHERE EXISTS
(SELECT 1 FROM WO_Status
WHERE Status IS NULL
AND WO_Status.ID = WO_Image.ID AND WO_Status.Type = WO_Image.Type)
And then
DELETE FROM WO_Image WHERE EXISTS
(SELECT 1 FROM WO_Status
WHERE Status IS NULL
AND WO_Status.ID = WO_Image.ID AND WO_Status.Type = WO_Image.Type)
Does that do what you want?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2008 at 10:03 pm
Whoa! Thanks!
And I had no clue about the EXISTS keyword.
Perfect! Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply