SQL SELECT REPLACE UPDATE COMMAND HELP

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

SQL SELECT REPLACE UPDATE COMMAND HELP

Author

Topic

sonia_newbie
Starting Member

17 Posts

Posted - 2012-10-14 : 15:40:28

Hi All,
I had a query related to sql select update replace command.

i have a table named clusters and it looks like this

name model characteristics
sample1.1 +123 parent
sample1.2 -456 clone
sample1.3 +122 clone
sample1.4 +12 parent
sample1.5 -14 parent
sample1.6 -12 clone
sample2.1 +45 parent

I want all my model values to be changed to +1 and -1
whichever model number are postive have to be +1 and whichever model number are negative have to be -1.
The table should look like this

clusters

name model characteristics
sample1.1 +1 parent
sample1.2 -1 clone
sample1.3 +1 clone
sample1.4 +1 parent
sample1.5 -1 parent
sample1.6 -1 clone
sample2.1 +1 parent

Is it possible using a select replace and update statement?
Please do help
i am not from programming background so still learning

Thanks !!
Sonia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:49:17

How you do this depends on what the data type of the column is. You can find out what the data type is using this query:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.[COLUMNS] 
WHERE TableName = 'Clusters' AND COLUMN_NAME = 'model';

If it is a numeric data type (such as INT, FLOAT, DECIMAL etc.), you would do the following:

UPDATE clusters SET model = -model;

If it is character type (such as VARCHAR, CHAR, NVARCHAR etc.), you would do the following:

UPDATE Clusters SET
	model = 
	CASE 
		WHEN model LIKE '+%' THEN REPLACE(model,'+','-')
		ELSE REPLACE(model,'-','+')
	END;

sonia_newbie
Starting Member

17 Posts

Posted - 2012-10-14 : 15:52:54

Hi,
Thanks for the reply!!The table name model has numeric values:
model
+123
-125
+13
and so on
I want to make them all +1 or -1.
Could I do something to change the numbers also to 1 but retaining their signs?

Thanks soo much :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:56:38

Ugh, I misread what you wanted :) Either of the following two queries should do what you want to do:

UPDATE clusters SET model = CAST(SIGN(model) AS INT);

-- OR 

UPDATE clusters SET
	model =
	CASE 
		WHEN model > 0 THEN 1
		WHEN model < 0 THEN -1
	END

In each case, if there are zeros, it will leave them unchanged.

sonia_newbie
Starting Member

17 Posts

Posted - 2012-10-14 : 16:41:51

Awesome!!
Thankyou so much!!

you solved my problem!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources