Login or Register to Ask a Question and Join Our Community


Programming

[Solved] SQL SELECT REPLACE

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming [Solved] SQL SELECT REPLACE
# 1  
Old 10-14-2012
Network [Solved] SQL SELECT REPLACE
Hi All,
I had a query related to sql select update replace command.

i have a table named clusters and it looks like this

Code:
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

Code:
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

Thanks !!
Sonia
Last edited by Corona688; 10-15-2012 at 12:05 PM.. Reason: delete this post becoz i got my solution :) thanks
# 2  
Old 10-15-2012
Try like...
Code:
 select test1.*, case when sign(model)=1 then 1 else -1 end from test1 
update test1 set MODEL=case when sign(model)=1 then 1 else -1 end

# 3  
Old 10-15-2012
MySQL
Thanks!!it worked!!! Smilie
Smilie
# 4  
Old 10-15-2012
I see no reason to delete your post, you asked a question and got a useful answer, future people with the same question may find this thread useful.
# 5  
Old 10-15-2012
Quote:
Originally Posted by sonia102
...
i have a table named clusters and it looks like this

Code:
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

Code:
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

...
What about records that have "model" value 0 ?
If you want to update those to 0 (in other words, keep them as they are), or if you are sure such values won't occur in your data, then your UPDATE statement could be simply -

Code:
update clusters set model = sign(model);

tyler_durden
These 2 Users Gave Thanks to durden_tyler For This Post:
jim mcnamara sonia102
# 6  
Old 10-22-2012
worked!! Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parse SQL text and only format first SELECT statement.

Hi Forum. Need your expertise on the following question. I have the following file which I would like to parse, find first block of SELECT statment and concatenate all input fields as 1 field (~ delimited): Old File: SELECT /*+ USE_HASH(CCOMM ICAR IMAP IAS IP IMAS IMPS IAP SPCA) */ ... (5 Replies)
Discussion started by: pchang
5 Replies

2. Programming

Sql select replace

Hi All, I had a query related to sql select replace command. i have a table named clusters and it looks like this Code: name characteristics sample 1.1 parent sample 1.2 ... (2 Replies)
Discussion started by: siya@
2 Replies

3. Programming

I'm going crazy with SQL select. Please help!

Hello people, as wrote in title I'm going crazy with a "complex" SQL select. This is the seelct: select T_ADDRESS, T_MCC,T_MNC,T_MSIN,T_IM_MNC, COUNT(*) FROM TABLETEST WHERE T_MCC=123 AND (T_MNC=11 OR T_MNC=01) GROUP BY T_ADDRESS,T_MCC,T_MNC,T_MSIN,T_IM_MNC HAVING count(*) > 5;This select... (7 Replies)
Discussion started by: Lord Spectre
7 Replies

4. Shell Programming and Scripting

[Solved] Select the columns which have value greater than particular number

i have a file of the form 9488 14392 1 1.8586e-07 5702 7729 1 1.8586e-07 9048 14018 1 1.8586e-07 5992 12556 1 1.8586e-07 9488 14393 1 1.8586e-07 9048 14019 1 1.8586e-07 5992 12557 1 1.8586e-07 9488 14394 ... (1 Reply)
Discussion started by: vaibhavkorde
1 Replies

5. Shell Programming and Scripting

SQL select all but not if it is already in an other column

I know I know.. for sure one of the easier mysql statements. But somehow I can not figure out this. I expect to see all distinct items of 'data_12' where 'kwroot' has 'straxx' in, and in the same row 'data_12' ist (not = 'kwsearched' in any existing row) data_12 ... (6 Replies)
Discussion started by: lowmaster
6 Replies

6. Shell Programming and Scripting

using SELECT sql statement in shell script

Hi there I have a database on a remote box and i have been using shell script to insert data into it for example, i could have a script that did this SN=123456 n=server1 m=x4140 sql="UPDATE main SET hostname='$n',model='$m' WHERE serial='$SN';" echo $sql |/usr/sfw/bin/mysql -h... (4 Replies)
Discussion started by: hcclnoodles
4 Replies

7. Shell Programming and Scripting

Select SQL Queries Option

count.sh#!/bin/ksh SQL1=`sqlplus -s usr/pwd @count.sql $1 $2 $3` SQL2=`sqlplus -s usr/pwd @selectall.sql $1 $2 $3` LIST="Count Select_All" select i in $LIST do if then echo $SQL1 elif then echo $SQL2 fi done (2 Replies)
Discussion started by: killboy
2 Replies

8. Shell Programming and Scripting

SQL Select and awk

Dear All , I have file1.txt contain values like the following: ---------- 23 24 25 and I have shell script which has the following : more file1.txt | awk '{print "select 'DUMP',CODE1||'|'||CODE2||'|'||CODE3 from CODE where CODE1='" $1 "';"}' > file2.sql all I need is to have the... (6 Replies)
Discussion started by: habuzahra
6 Replies

9. Web Development

SQL Select inside Insert

I have following. . . . $userid = 2 . $query = "select username from users where userid = ".$userid.";"; . $username = $line; $data="Some Data Here"; . $query = "insert into logger (username, data) valuse ($username, $data);"; . I would like to not have 2 database calls. (3 Replies)
Discussion started by: Ikon
3 Replies

10. Shell Programming and Scripting

formatting the sql select result

Hi, I have about 12 columns and 15 rows to be retrived from sybase isql command through unix. But when i output the sql into a file and see it, the formatting is going for a toss. can someone please suggest how can i get the result correctly in the output file ? Thanks, Sateesh (2 Replies)
Discussion started by: kotasateesh
2 Replies
Login or Register to Ask a Question