DELETE STATEMENT FROM SELECT RESULTS

Hi all please help.

I'm trying to delete the selected data from a table colum from a select statement.

this is the select statement

SELECT  RFC822  FROM  SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')

Can somebody please tell me what the delete statement would look like please. I've tried replacing Select with delete from but get a sytax error.

regards

Joe

July 24th, 2015 11:11am

Try:

;with cte as (SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB

WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24')

OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')) delete from cte

Assuming that RFC822 is a primary key in that table.
Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 11:13am

Also..

DELETE From SQLGOLDMINE.DBO.MAILBOX WHERE RFC822 IN

(

SELECT  RFC822  FROM  SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')

)

July 24th, 2015 11:15am

Hi, 

Do something like

delete from tab1 t1 where exists(select * from tab2 t2 where t2.col1=t1.col1 ...)

Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 11:20am

The syntax error may have happened if you had the column name between delete and from.

Try this:

DELETE FROM SQLGOLDMINE.DBO.MAILBOX MB 
WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')

Here's the documentation on the delete command: https://msdn.microsoft.com/en-us/library/ms189835.aspx

July 24th, 2015 11:23am

Be very careful about what you are doing.  And you should verify that you have a good, recent backup before you go further. 

Why?  One deletes rows, not columns.  A delete statement will remove entire rows from your table.  Your select statement only returns a single column (of some unknown number of rows).  So is your intent to remove the entire row that contains the single (presumably) value you see in the resultset of your select statement?  Or is your goal to simply set the value of the column in this/these rows to an empty string (or null or some suitable other value)? 

Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 11:40am

>>  to delete the selected data from a table column from a select statement. <<

NO! NO! NO! SQL is a set oriented language. We delete entire rows from a table. It is not physuically possible to remove a column; all the rows have to be the same. 

>> Can somebody please tell me what the delete statement would look like please. <<

DELETE FROM Mailbox 
WHERE EXISTS
(SELECT *
  FROM Mailbox AS MB 
 WHERE '2014-07-24' IN (MB.creation_date, MB.mail_date) 
   AND MB.mail_ref LIKE '%auction notification & invitation%'
   AND MB.rfc822 = Mailbox.rfc822 ) ;
July 24th, 2015 1:29pm

Why do you need exists and not just delete with conditions? 
Free Windows Admin Tool Kit Click here and download it now
July 24th, 2015 1:33pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics