how to update data in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to update data in a query

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 14:57:20

I have a set of querys that take up about two pages. within those pages there are about 100 dif programids that I want to replace with new programids equal distance numbericaly appart. for example 1 is being changed to 11.....2 to 12 and so on. I attempted to find and replace but it is not reading each programid by itslef.

, 1824, 1825, 1826, 1827, 1828, 1829, 1830, 1831, 1832, 1833, 1834, 1835, 1836, 1837, 1838, 1839, 1840, 1841, 1842, 1843, 1844, 1845, 1846, 1847, 1848, 1849

replace with....

1913 , 1914, 1915, 1916, 1917, 1918, 1919, 1920, 1921, 1922, 1923, 1924, 1925, 1926, 1927, 1928, 1929, 1930, 1931, 1932, 1933, 1934, 1935, 1936, 1937, 1938, 1939

I am going to have to do this over and over and do not want to have to type each one at a time..

Please help! :0

best,
GG

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 15:12:25

DECLARE @newOffset int; SET @newOffset = 10

UPDATE myTable99
SET ProgramID = ProgramID + @newOffset

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 15:13:19

Is the offset always the same?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-12 : 15:14:53

This?
update table
set programid = programid+89
where programid between 1824 and 1849

No, you're never too old to Yak'n'Roll if you're too young to die.

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 15:21:36

Not sure if I follow......but I like the idea there is somthing out there! here is a chunk of what I am trying to update.

--- RCV/ACV *** 1=RC/AC, 2=RC/RC, 3=AC/AC, 4=AC/RC
SELECT RateData.RateID, RateData.ProgramID, RateData.CoverageID, RateData.LimitMin, RateData.LimitMax, RateData.Rate,
RateData.Territory, Programs.Policytype, Programs.ConstructionType
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where ratedata.programid Between 1823 and 1912 and DCmax > .19 and ratedata.coverageid = 7
order by Policytype

SELECT RateData.RateID, RateData.ProgramID, RateData.CoverageID, RateData.LimitMin, RateData.LimitMax, RateData.Rate,
RateData.Territory, Programs.ConstructionType
FROM RateData INNER JOIN
Programs ON RateData.ProgramID = Programs.ProgramID
where ratedata.programid Between 1823 and 1912 and DCmax > .19 and ratedata.coverageid = 2 and programs.constructiontype = 1
order by territory

I need to change 1823 to 1913 and 1912 to 2002 but there are about 100 other numbers that need to be changed.....how to I replace the the numbers in the query so I can run my updates against my tables

GG

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 15:28:42

So I already have all the tables updated where the new programids are in the table.......but I am trying to update the programids on the query I have saved.......

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 15:32:13

Wait, so you need to change the text of the queries, not the actual underlying data?

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 15:37:12

this is what I used to update the tables. but now I need to update the pages of queries.....can I run updates within the query box? The find replace solution sucks......its going to take me forever. Any Ideas????

best,
GG

---Converting sc into NJ the Where is the now and the state is the to be....the rest should be an increse of 90
INSERT INTO Programs (ProgramID, ApplicationID, CompanyID, State, Description, OccupancyTypeID, PolicyType, PolicyTerm, DCMin, DCMax, MinLosses, MinValue, MaxCredits, MaxPDCredit, MaxSchedule, ConstructionType, ClassCode, TIVLimit)
(SELECT ProgramID+8669, ApplicationID, CompanyID, State='NJ', Description, OccupancyTypeID, PolicyType, PolicyTerm, DCMin, DCMax, MinLosses, MinValue, MaxCredits, MaxPDCredit, MaxSchedule, ConstructionType, ClassCode, TIVLimit
FROM Rating_2.dbo.programs
WHERE ProgramID BETWEEN 1331 AND 1420)

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 15:37:49

JDQ: DBA

1. If it's too much work, you're doing it wrong
2. Always be a pessimist
3. Repeats steps 1 & 2
4. Drink Tequila

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJins
Yak Posting Veteran

81 Posts

Posted - 2010-11-12 : 15:37:59

TIMSMAN.....you are right on how do I do that????

Best,
GG

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-12 : 15:39:10

so, you know how to add to the ID by the range it's in?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-12 : 15:43:15

I would use a text editor that has some find/replace capability.

OR....

Rewrite the queries so that you can set variable(s), and use that throughout the code. I like this option more.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources