CODEWITHSUNDEEP
sqlnetezza
user3494110
user3494110

Reputation: 427

SQL (NZ) How to find decimals in a numeric column

I am trying to put some SQL together to update Table_2 based on Table_1's values, but the catch is that it's only when the value is a decimal and not a whole number. 

Table_1                      Table_2

Col_A  Col_B  Key          Col_C  Col_D  Key
3.2     6     331            3      6    331
5       8     332            5      8    332
1.12    9     333            1      9    333
6       10    334            6      10   334

So referring to the tables above what I'd like to do is update Table 2's Col_C to match Col_A based on the key but only if Col_A is a decimal and not a whole number.

I know you could just do a blanket update in this case (update all of table 2 to match table 1) but in the real application I'm using it for it won't work I need to do it for only decimals, just not sure how to narrow them down. Col_A/C are Numeric(10,4). Col B/D are int.

Just a note this is in Netezza, not sure if NZ has something special that makes it easier to do this.

Upvotes: 0

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

This require using some mathematical functions, particularly floor():

update table_2
    set . . .
from table_1
where table_2.col_C = floor(table_1.col_A) and
      table_1.col_A > floor(table_1.col_A);

Upvotes: 1

Related Questions