how to calculate a column using another column in sql?

Question

the create table query is :

create table catalog (id int, ra double, decl double , zone int);

the zone value is calculated from decl using formula below:

CAST(FLOOR(decl) AS INTEGER),

i have insert all id,ra,decl values of the table, then I have to calculate zone values,

how to calculate the table's zone value in a sql query?

**in another table extractedcatalog, i want to calculate y from ra,decl in sql:

update extractedcatalog set x= (cos(radians(decl))cos(radians(ra))); but responsed: connection terminated! is there any problem with my sql?*

Thanks very much!

Two points. First, floor() returns an integer so the cast() is redundant. Next, one of the rules of normalization is to not store calculated values. Just select the calculation when you need it. — Dan Bracuk, Nov 19 '13 at 12:49

score 0 · Answer 1 · answered Nov 19 '13 at 12:50

0

you will want to do something like this:

update catalog set zone = CAST(FLOOR(decl) AS INTEGER);

answered Nov 19 '13 at 12:50

Brett Schneider

vogomatix · Accepted Answer · 2013-11-19T12:56:14.887

0

Haven't you done it already?

update catalog set zone=CAST(FLOOR(decl) AS INTEGER);

if this doesn't work, try doing it as a self join:

update catalog set zone=CAST(FLOOR(a.decl) AS INTEGER)
from catalog a
where 1=1;

I don't have a DB instance available at the moment so you'll have to check these yourself.

As another person has pointed out, you don't really need to store zone at all, you can just return it as a calculated value

select CAST(FLOOR(a.decl) AS INTEGER) as zone
from catalog a
where 1=1;

edited Nov 19 '13 at 12:56

answered Nov 19 '13 at 12:50

vogomatix

What makes you think the db engine is mysql? The OP didn't specify anything. – Dan Bracuk Nov 19 '13 at 12:53
I don't have any DB engine in front of me at the moment but the answers are fairly generic SQL and should work on any DB with little to no modification. I've corrected my answer to reflect your nitpick :-P – vogomatix Nov 19 '13 at 12:54
could you tell me what does 1=1 mean? – user2706760 Nov 19 '13 at 13:11
Its a method of having a where clause that selects all rows. It may not be needed – vogomatix Nov 19 '13 at 13:20
*in another table extractedcatalog, i want to calculate y from ra,decl in sql: update extractedcatalog set x= (cos(radians(decl))cos(radians(ra))); but responsed: connection terminated! is there any problem with my sql? ra,decl are all inserted into tables. – user2706760 Nov 19 '13 at 13:21
You said you want to calculate 'y' but your SQL is setting 'x'. Which is it? :-) – vogomatix Nov 19 '13 at 13:23

2 Answers2