Replacing data - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Replacing data

Author

Topic

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-20 : 16:54:56

Hi

I have this view that I pulled data from different tables in my database.

What I want to do is where the TR column is Y the value in the TR_R column needs to be a zero value. Same with the QR column.

Thx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 16:58:17

Use CASE statement.

SELECT CASE WHEN TR = 'Y' THEN 0 ELSE TR_T END AS TR_T,
TR,
CASE WHEN QR = 'Y' THEN 0 ELSE QR_R END AS QR_R,
QR
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

ewaldbruwer
Starting Member

6 Posts

Posted - 2008-06-20 : 17:07:50

I want the data in the database to be replaced by 0.

Will this solution do it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-20 : 17:11:22

No, you will have to UPDATE to alter values in a table in the database.
Something similar to

UPDATE Table1
SET TR_T = CASE WHEN TR = 'Y' THEN 0 ELSE TR_T END,
QR_R = CASE WHEN QR = 'Y' THEN 0 ELSE QR_R END

Make sure you have a recent backup first.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 19:03:12

or use two updates

Update Table1
set TR_R=0
where TR='Y'

Update Table1
set QR_R=0
where QR='Y'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources