Replace a number in a row(multiple fields)

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

SQL Server 2000 Forums

Transact-SQL (2000)

Replace a number in a row(multiple fields)

Author

Topic

Qnavry
Starting Member

4 Posts

Posted - 2008-08-28 : 13:40:06

I have a table that i need to check at the end of each month. If the date field is older that the current month and day i need to change a number (add one to it) and then update the recorded if it does not already exist. I have about 5 fields in each row that will have the same number that needs to change. What is the easiest way to do this.

example

id desc date name title
xxx8=nnn 2008-testdata 200808 bob mr08
yyy8=ttt 2008-testdata2 200808 betty mrs08
xxx9=nnn 2009-testdata 200908 bob mr09

so if i select the date older that sept 2008 i will return the top 2 rows. I want to change the 8 to a 9 except for the month (08) in the date field.

so it would be:

id desc date name title
xxx9=nnn 2009-testdata 200908 bob mr09
yyy9=ttt 2009-testdata2 200908 betty mrs09

well the first record already exist so i don't want to update I can just delete it. I need to add/update the second record to the table.

Does this make any sense? What is the easiest way to accomplish this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 14:01:25

UPDATE t
SET t.date=CONVERT(varchar(6),CONVERT(int,date) + 100)
FROM
(SELECT *
 FROM YourTable
 WHERE MONTH(date)<MONTH(GETDATE())
 AND YEAR(date)=YEAR(GETDATE()))t
LEFT JOIN
(SELECT *
 FROM YourTable
 WHERE MONTH(date)>MONTH(GETDATE())
 AND YEAR(date)>=YEAR(GETDATE()))t1
ON t1.desc=t.desc
AND t1.name=t.name
WHERE t1.desc IS NULL
AND t1.name IS NULL

DELETE t
FROM
(SELECT *
 FROM YourTable
 WHERE MONTH(date)<MONTH(GETDATE())
 AND YEAR(date)=YEAR(GETDATE()))t
INNER JOIN
(SELECT *
 FROM YourTable
 WHERE MONTH(date)>MONTH(GETDATE())
 AND YEAR(date)>=YEAR(GETDATE()))t1
ON t1.desc=t.desc
AND t1.name=t.name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources