Solved

sql delete

Posted on 2013-05-16
4
141 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

809 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