Solved

sql delete

Posted on 2013-05-16
4
138 Views
Last Modified: 2013-06-10
I need to be able to delete a contents of a column but only if it is over 50 weeks old I have the 50 weeks on bit but not sure how to remove contents from it
     (Crtd BETWEEN CAST(CAST(DATEADD(week, - 50, GETDATE()) AS date) AS datetime) AND DATEADD(second, - 1, DATEADD(day, 1, CAST(CAST(DATEADD(week, 
                         - 51, GETDATE()) AS date) AS datetime))))

Open in new window

0
Comment
Question by:beridius
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Ross Turner earned 500 total points
ID: 39171165
You just update the column fields to null using your where

Update youtable 
set yourcolumn = null 
where  (Crtd BETWEEN CAST(CAST(DATEADD(week, - 50, GETDATE()) AS date) AS datetime) AND DATEADD(second, - 1, DATEADD(day, 1, CAST(CAST(DATEADD(week, 
                         - 51, GETDATE()) AS date) AS datetime))))

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39171169
The term DELETE refers to entire rows, not the contents of a column.  
It's an UPDATE to delete contents of a column...

UPDATE YourTable
SET a_column = NULL
WHERE {the 50 weeks on bit}
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39171242
Hi Beridius

i've create this example for you

http://sqlfiddle.com/#!3/17028/4

Create table test 
(
 VALID_FROM varchar (30),
 VALID_TO varchar (30),
 TXT varchar (30)
)

INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-05-14 00:00:00.000','2012-11-13 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-05-14 00:00:00.000','2012-11-13 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-06-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-04-01 00:00:00.000','2013-04-01 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-04-01 00:00:00.000','2013-04-01 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-03-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2012-07-01 00:00:00.000','2013-06-30 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-05-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-05-01 00:00:00.000','2013-05-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2013-06-30 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2014-03-31 00:00:00.000','Test')
INSERT INTO test(VALID_FROM,VALID_TO,txt) VALUES('2013-04-01 00:00:00.000','2014-03-31 00:00:00.000','Test')

Open in new window


-- Select 

Select
VALID_FROM,
VALID_TO ,
TXT,
DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) AS DiffDate
from test;

-- Update 

update test
set txt = null
where DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) > 51;

-- Select 

Select
VALID_FROM,
VALID_TO ,
TXT,
DATEDIFF(week,cast([VALID_FROM] as datetime),cast([VALID_TO]as datetime)) AS DiffDate
from test;

Open in new window

0
 
LVL 2

Author Comment

by:beridius
ID: 39171390
I will give this a try thanks guys
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now