CODEWITHSUNDEEP
sql-server
kickster09
kickster09

Reputation: 41

Trying to REMOVE data from a COLUMN

I'm trying to get rid of all the CPAC_FR as showed below

Facility Response   CPAC_FR Transfer Call        2016-03-07         1
Facility Response   CPAC_FR Fulfillment-Email   2016-03-04  4
Facility Response   CPAC_FR Fulfillment-Email   2016-03-10  1
Facility Response   CPAC_FR Fulfillment-Email   2016-03-17  2
Facility Response   CPAC_FR Fulfillment-Email   2016-03-21  2

Here's my code. I'm still learning how to write query so I keep getting stuck on things.

SELECT  SR_AREA, INS_PRODUCT, RESOLUTION_CD, CAST(CREATED AS DATE) AS DATEADD
      , COUNT(*) AS TOTAL
FROM    S_SRV_REQ WITH (NOLOCK)          
WHERE   (dbo.fn_dstoffset(CREATED) >= '3-1-2016')  
AND (dbo.fn_dstoffset(CREATED) <= DATEADD(D, 1, '3-31-2016'))  
AND  [SR_AREA] IS NOT NULL 
GROUP BY INS_PRODUCT, RESOLUTION_CD, SR_AREA,  CAST(CREATED AS DATE) 
ORDER BY SR_AREA,INS_PRODUCT,RESOLUTION_CD DESC

I'm leaning more into adding a DELETE FROM .. WHERE ..

I'm using Microsoft SQL Server Management Studio.

Upvotes: 0

Views: 51

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Delete will not work with a group by
Start with a select and be sure those are the rows you want to delete 

SELECT  SR_AREA, INS_PRODUCT, RESOLUTION_CD, CAST(CREATED AS DATE) AS DATEADD
FROM    S_SRV_REQ        
WHERE   (dbo.fn_dstoffset(CREATED) >= '3-1-2016')  
AND     (dbo.fn_dstoffset(CREATED) <= DATEADD(D, 1, '3-31-2016'))  
AND     [SR_AREA] IS NOT NULL

then just change the select .... line to delete 

DELETE 
FROM    S_SRV_REQ        
WHERE   (dbo.fn_dstoffset(CREATED) >= '3-1-2016')  
AND     (dbo.fn_dstoffset(CREATED) <= DATEADD(D, 1, '3-31-2016'))  
AND     [SR_AREA] IS NOT NUL

Upvotes: 1

Igor
Igor

Reputation: 62213

When working with a single table - Once you have your SELECT statement returning only the data you want to actually delete you can then replace the 'SELECT with DELETE and remove the clauses GROUP BY and ORDER BY. In your example you would end up with this:

DELETE
FROM    S_SRV_REQ
WHERE   (dbo.fn_dstoffset(CREATED) >= '3-1-2016') 
AND (dbo.fn_dstoffset(CREATED) <= DATEADD(D, 1, '3-31-2016')) 
AND  [SR_AREA] IS NOT NULL

Side Notes:

  • Take FK constraints into consideration, the update might fail because of a constraint or might cascade delete constraining records depending on the FK constraint setting.
  • It is also advisable to always test DELETE statements in a non production setting first to ensure they behave as expected.
  • Finally it is also never a bad idea to let a co-worker review a DELETE statement before execution in a production environment.

Edit

I am assuming by this Trying to delete data from a row you mean Trying to delete data from a table. You cannot delete data from a row although you can update a value in a row to NULL if that column allows for nullable data/value. If that is the case update your question accordingly.

Upvotes: 1

Related Questions