question

Eileen 1 avatar image
Eileen 1 asked

Update column value based on other columns in same table

I have a table that looks something like the following:

Row Cust date1 date2 key daysdiff

1 A 1/1/2010 1/14/2010 x

2 A 1/21/2010 1/31/2010 y

3 B 1/7/2010 1/10/2010 x

4 B 2/1/2010 2/3/2010 y

I need to loop through the table and calculate the value for the 'daysdiff' column based on the values in the 'Cust' and 'key' columns. Cust must equal Cust, and key must equal 'y'.

So, in the above example, the daysdiff value for row 2 should equal 7, and for row 4, it should be 22. There should be null values for rows 1 and 3.

I think I need a self join with my update statement, but I'm not getting it quite right. Any assistance is welcome.

Thanks in advance.

updatejoins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Sounds like a bit of homework. You don't need to do a self-join. You just need to follow the syntax of UPDATE... FROM... and refer to the table twice. There's also a function called DATEDIFF that can help.

I'd get more specific, but I don't like providing homework answers unless I'm getting the grade too.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Right there with Grant. Update the table and reference it twice - no need to Join.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
I was thinking about it, is there actually a way to not self join if the question is about updating a column by calculating values from the current and previous record? The question is about datediff between the date2 of the "x" and date1 of the "y" record... Still agree on the homework.
0 Likes 0 ·
Oleg avatar image
Oleg answered

I totally agree with Grant and Jason, but just in case if all the records in the table are uniformly bunched into couples just like you describe (you always have 2 records per customer), you can then use the following join to update every second record (marked as y) such that its daysdiff column gets the value of the difference between date2 of the previous record and date1 of the current one:

update a
    set daysdiff = datediff(day, b.date2, a.date1)
    from dbo.test a inner join dbo.test b
        on a.cust = b.cust and a.[key] = 'y' and b.[key] = 'x';

This will only work with the scenario I assummed.

Oleg

6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
If the person demonstrates effort to answer the question and is stuck - then that is fine. If it does not appear that they have given a good effort, then encourage them to do some of the leg-work. If they then do some of the leg-work, by all means help out. Just don't give away homework answers freely.
2 Likes 2 ·
Oleg avatar image Oleg commented ·
In this case I should be OK. The end of the question reads "I think I need a self join with my update statement, but I'm not getting it quite right. Any assistance is welcome", which clearly shows leg-work and good understanding of the task.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
I have no issues helping people with their homework, but I really do want to see demonstrated work before I pitch in and supply answers. This one showed some thought about the issue, but didn't show that they had done some work. Either way, I like your answer better than mine.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Nice, but if this is homework, you gave away the whole answer.
0 Likes 0 ·
Oleg avatar image Oleg commented ·
Actually, until now, I did not realize that there are homework questions on this site. Now when I recall a question about a week ago asking how to split a single record into 2 based on some apples, bananas and oranges, I suspect that it was a homework and I did provide the whole answer at that time. However, assuming that whoever asks the question first spends some time trying to solve the problem before asking for help, then I guess it is OK to get the answer from someone, right?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.