CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10gsql-update
HereToLearn_
HereToLearn_

Reputation: 1180

How to update a boolean field in Oracle table

I am a novice Oracle user. I wanted to update a boolean field in my table for one of the record. Which one of these statements is correct ?

update MyTable set myBooleanColumn = 1 where UserId= 'xx12345';

or 


update MyTable set myBooleanColumn = '1' where UserId= 'xx12345';

any help is greatly appreciated!! thanks !

Upvotes: 3

Views: 16640

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172468

There is nothing as Boolean field in Oracle.

The best what you can do is to create the table like this:-

create table ABC(bool char(1) check (bool in ('N','Y'));

Then simple update like

UPDATE ABC set bool = 'Y';

WHY TAKING CHAR?

There is no support for BOOLEAN, BIT, or TINYINT data types so char would be the best as it takes 1 byte

Upvotes: 1

DHW
DHW

Reputation: 1196

It depends on how the field is defined.

If its defined as a CHAR(1) field, then you can store 'Y'/'N' or 'T'/'F' in it. To update the field, you'd use the quotes as it would be a string literal.

UPDATE TestTable set myCharBooleanColumn = 'Y';

If the field is defined as NUMERIC, then the convention is 0=false and 1 or -1 is true (I've seen both). 

UPDATE TestTable set myNumericBooleanColumn = 1;

Many people will advocate the CHAR(1) approach, but in the real world - you see both. It depends on how the boolean is implemented.

You can read more in Oracle's docs on Datatypes http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm

Upvotes: 8

Ben
Ben

Reputation: 52883

There is no such thing as a Boolean field in Oracle so your field is either a numeric field or a character field. If it's a numeric field you don't need to quote the number; if it's a character field you should quote the string.

You can find out the type of the column by querying USER_TAB_COLUMNS:

select *
  from user_tab_columns
 where table_name = 'MYTABLE'
   and column_name = 'MYBOOLEANCOLUMN'

or by describing the table.

Upvotes: 4

Related Questions