WHERE in WHERE

[JohnnyLong]

Is this possible?

I'm using SQL7.

I have a table like this:

ID WeekCount WeekCommencing

001 1 28/02/2005
002 2 07/03/2005


I want to replace WeekCommencing 28/02/2005 with WeekCommencing 07/03/2005.

I came up with this pseudo sql code :

UPDATE Whereabouts
SET (WeekCommencing (WHERE WeekCount = 1)) = (WeekCommencing (WHERE WeekCount = 2))

I know this is wrong, but can it be done?

John

 

[hmckillop]

in english what is it you want to do.

Is it you want to replace the weekcommencing field in any row with weekcount =2 with weekdcommencing field in any row where weekcount = 1
if so you can do

UPDATE t1
SET t1.Weekcommencing =t2.Weekcommencing
FROM WhereAbouts t1
INNER JOIN (SELECT weekcount -1 as Wk, weekcommencing
  FROM WhereAbouts where weekcount = 2) t2 on t2.wk = t1.weekcount
Where T1.Weekcount=1

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JohnnyLong]

That's perfect. Just what I needed. You certainly came back with that quickly.

Thanks alot

 

[JohnnyLong]

hmckillop,

Following on from my first post I was just wondering if you could suggest a more elegant way of doing the following:


/***Replace Week1 with Week2***/
UPDATE t1
SET t1.Weekcommencing = t2.Weekcommencing
FROM WhereAbouts t1
INNER JOIN (SELECT weekcount -1 as Wk, weekcommencing
FROM WhereAbouts where weekcount = 2) t2 on t2.wk = t1.weekcount

Where T1.Weekcount=1


/***Replace Week2 with Week3***/
UPDATE t1
SET t1.Weekcommencing = t2.Weekcommencing
FROM WhereAbouts t1
INNER JOIN (SELECT weekcount -1 as Wk, weekcommencing
FROM WhereAbouts where weekcount = 3) t2 on t2.wk = t1.weekcount

Where T1.Weekcount=2


/***Replace Week3 with New Week***/
UPDATE Whereabouts
SET Weekcommencing = DATEADD(day,7,Weekcommencing)

Where Weekcount=3

Thanks

 

[hmckillop]

Is this going to be a recursive thing, i.e. do you plan then to replace week4 with week5 etc. or is it fixed 3 week?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JohnnyLong]

It's fixed to 3 weeks. I have an asp.net datagrid showing the whereabouts of our company employees for last week, this week and next week. Once this week rolls over to next week I need to update the dates as above. The procedure above works fine but is probably not the best way.

John

 

[hmckillop]

Effectively all you are doing is a dateadd to each week, so the simplest way is to

UPDATE Whereabouts
SET Weekcommencing = DATEADD(day,7,Weekcommencing)

As you always update the last weeke i.e. weekcount=3 with week add, this implies you do the same for the previous weeks, so the above query will add one week for each of the 3 weeks and thus producing the same result.


"I'm living so far beyond my income that we may almost be said to be living apart