SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL SELECT REPLACE UPDATE COMMAND HELP		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonia_newbie
Starting Member

India
17 Posts
Posted - 10/14/2012 :  15:40:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/14/2012 :  15:49:17  Show Profile  Reply with Quote
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;
Go to Top of Page

sonia_newbie
Starting Member

India
17 Posts
Posted - 10/14/2012 :  15:52:54  Show Profile  Reply with Quote
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 :)
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/14/2012 :  15:56:38  Show Profile  Reply with Quote
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.
Edited by - sunitabeck on 10/14/2012 15:57:59
Go to Top of Page

sonia_newbie
Starting Member

India
17 Posts
Posted - 10/14/2012 :  16:41:51  Show Profile  Reply with Quote
Awesome!!
Thankyou so much!!

you solved my problem!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000