Using DateAdd() to Add 3 days to Every Row in Table

[stanlyn]

Hi,

What is the easiest way to iterate through a MSSQL table and set the value of date2 to whatever the value of date1 is plus 3 days? A sample showing how to use it with the update-set commands along with the where clause. I've googled for some examples with no luck on how to iterate.

Thanks,
Stanley

 

[Mike Lewis]

Stanley,

Reading back over this thread, I feel sure that both Andy and myself have explained the two broad options (using UPDATE or having a computed column) in simple, plain language. I'm sorry you still don't get it. I can only suggest that you experiment a bit more and carefully observe the results. I would hope that it would then all become clear.

I noticed in your first post in this thread, you remarked "I've googled for some examples with no luck on how to iterate." Maybe that's the root of the problem. If your approach to problem solving is to google for examples, you will get nowhere. You would do better to sit down with a manual or a reference book, read it carefully, and learn the fundamentals of the language or whatever from the ground up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Andrzejek]

My initial question involves tables where I need to find date1 in table2 and update date2 in table1

That's news to me. There was no mention of table2 in your original (or any other) post:

set the value of date2 to whatever the value of date1 is plus 3 days

I am with Mike. Maybe reading our posts and - if there are still questions - re-state your requirement, but not by 'how to do it' but by 'what needs to happen'.

Believe it or not, but we just try to help.

>What doesn't make sense is changing the value with a select statement that has an "as" keyword with no assignment as previously mentioned.
What 'select' statement are you referring to

---- Andy

There is a great need for a sarcasm font.

 

[bborissov]

I always thought we use "select" to select rows, "update" to update/change them.

No, you can not change rows You can change data in them.
Computed columns are just an easiest way to get what you want,
Instead every time to write something like this

SELECT DateField
      , DateAdd(day,3, DateField) AS CalcDate
FROM ....

You can add computed column as Mike suggested.
But If you cant to UPDATE the date field based on some condition you can use WHERE clause in your UPDATE statement.
The you will limit the records affected to these which match the condition.

If you want to UPDATE the records based on some other table condition the you should use FROM and JOIN clauses:

UPDATE Table1 SET DateField = DATEADD(day, 3, DateField)
FROM Table1
INNER JOIN Table2 ON .....

Borislav Borissov
VFP9 SP2, SQL Server