Update using TO_NUMBER

From: <inlove22_at_hotmail.com>
Date: 15 Jan 2006 14:28:34 -0800
Message-ID: <1137364114.760810.11320@g43g2000cwa.googlegroups.com>

Hello,

I have a problem with one of my update queries. Im trying to update table 1 column x_number which is datatype number() using the data in table 2 column x_char which is datatype char(). The following query does not work as i get the error value is not a number. Basically i wondering how i can convert the value of x_char as a number and stored it as a number into x_number.

TIA!!!!

---

---

UPDATE table1
SET x_number = (SELECT TO_NUMBER(x_char)

		FROM table2 WHERE  LENGTH(x_char) = 11
		AND row_id = table1.par_row_id
		AND x_char is not null
WHERE EXISTS  (SELECT x_char
		FROM table2 WHERE  LENGTH(x_char) = 11
		AND row_id = table1.par_row_id
		AND x_char is not null

);

commit;

EXIT;

---

---

Received on Sun Jan 15 2006 - 16:28:34 CST