Need to combine a Delete stmt and Select stmt

[tobermory20]

Hello,
I want to delete information from one table why using another table... Here it was the code looks like now...

delete from hl_data where type_number=4 and (select software_name from hl_software where software_name='" & lsSoftwareName & "') "

Here is what I wrote... but I am getting syntax errors at the '(' ... can someone help?

DELETE FROM hl_data
WHERE     (SELECT     software_name
           FROM       hl_software
           WHERE      (dbo.HL_Software.Software_Name = 'Help Subsystem') AND (dbo.HL_Data.Type_Number = '4') AND (dbo.HL_Data.Type_ID = '5'))

 

[r937]

can you say in words what you want to achieve?

also, are you sure this is mysql? it looks like mssql

;-)

r937.com | rudy.ca

 

[tobermory20]

I should have cleaned my code better, but yes I would like to acheive this code to work simular to this...

DELETE FROM HL_Data
WHERE     (Type_Number = '4') AND (Type_ID = '5') AND
 (SELECT     Software_Number, Software_Name
  FROM          HL_Software
  WHERE      Software_Name = 'Help Subsystem')

 

[r937]

AND (SELECT) doesn't do anything for me

why do you have the SELECT?

i understand this part --

DELETE
FROM HL_Data
WHERE Type_Number = '4'
AND Type_ID = '5'

but what does the other table have to do with deleting from this one?



r937.com | rudy.ca

 

[tobermory20]

at last this works, I am deleteing information about a record named Help Subsystem this info is in two different tables, so all info related to Type_Number = '4' in one table that is related to all info in the Help Subsystem... so I am aligning information up so to delete

DELETE FROM dbo.HL_Data
WHERE (Type_Number = '4') AND (Type_ID IN
  (SELECT     Software_Number
   FROM          dbo.HL_Software
   WHERE      (Software_Name = 'Help Subsystem')))