SQL Server - Select list and update a field in each row

Question

I hate to ask something that I know has been answered, but after 45 minutes of searching I yield to everyone here.

I have a table where I need to select a list of items based on a value, and then take one field and add a year to it and update another column with that value.

Example:

select * from table where description='value_i_need'

UPDATE table SET endDate=DATEADD(year, 1, beginDate) -- For each item in the select

EDIT: The select statement will return a ton of results. I need to update each row uniquely.

I don't know how to combine the select or loop through the results.

My SQL knowledge is limited, and I wish I could have figured this out without having to ask. I appreciate any help

Edit 2:
I have one table that I need to go through and update the end date on each item that matches a description. So I have 1000 "Computers" for example, that I need to update a field in each row with that description based on another field in the same row. All coming from one table

score 1 · Answer 1 · answered Jan 31 '14 at 15:35

1

Unless I missed something, you should be able to simply add your criteria to your update statement as follows:

UPDATE [table]
SET endDate=DATEADD(year, 1, beginDate)
WHERE description='value_i_need'

answered Jan 31 '14 at 15:35

Matt Klinker

773
5
18

score 1 · Answer 2 · answered Jan 31 '14 at 15:38

1

UPDATE  T
SET     T.END_DATE = DATEADD(YEAR,1,T.BEGINDATE)
FROM    TABLE T
WHERE   T.DESCRIPTION = 'value_i_need

answered Jan 31 '14 at 15:38

A.J

382
2
6

Thank you, I had a misunderstand that I needed to somehow manually loop through things – JeremyK Jan 31 '14 at 16:05

score 1 · Answer 3 · answered Jan 31 '14 at 15:38

1

Do you mean:

UPDATE [TABLE] SET ENDDATE=DATEADD(YEAR,1,BEGINDATE) WHERE DESCRIPTION='VALUE_I_NEED'

answered Jan 31 '14 at 15:38

Punter015

1,718
10
13

Vignesh Kumar A · Accepted Answer · 2014-01-31T15:54:56.277

1

Try like this

UPDATE
    Table T1
SET
    T1.endDate=DATEADD(year, 1, beginDate)

WHERE T1.description='value_i_need'

edited Jan 31 '14 at 15:54

answered Jan 31 '14 at 15:45

Vignesh Kumar A

27,863
13
63
115

I only have one table, what do I need to change in your answer – JeremyK Jan 31 '14 at 15:52
Isn't your answer still under the assumption I am using multiple tables? – JeremyK Jan 31 '14 at 15:55
I have one table that I need to go through and update the end date on each item that matches a description. So I have 1000 "Computers" for example, that I need to update a field in each row with that description based on another field in the same row. All coming from one table – JeremyK Jan 31 '14 at 15:56
You can use **JOIN** with all tables – Vignesh Kumar A Jan 31 '14 at 15:56
@JeremyK Above query will update all columns which match a description – Vignesh Kumar A Jan 31 '14 at 15:59
I didn't think it would be that simple, didn't realize it would update each row accordingly. Thank you – JeremyK Jan 31 '14 at 16:03
@JeremyK Man. I am saying this from starting itself – Vignesh Kumar A Jan 31 '14 at 16:05
This will be a good read for you to understand looping vs set based operations: http://stackoverflow.com/questions/1687512/rbar-vs-set-based-programming-for-sql – TTeeple Jan 31 '14 at 16:21

SQL Server - Select list and update a field in each row

4 Answers4