Alex Katebi wrote:
>
> I was woundering how I can update a column in my table back to its default
> value.
>
> For example:
>
> create table t1 (value integer default 55, name text);
> insert into t1(name) values('hello');
> update t1 set value=default; /* for illustration only */
>
This will update every row in the table, not just the one with the name
'hello'. Is that what you want?
> How can I achive the desired behavior short of doing "update t1 set
> value=55"
>
I don't think this can be done using an update unless you hard code the
value as you have shown above.
The default value is only used when a row is inserted into the table and
a value isn't provided for that column, so you will have to arrange for
that by deleting the row and then inserting it again.
delete from t1 where name = 'hello';
insert into t1(name) values('hello');
An insert or replace also works since internally it does a delete and
then an insert when doing a replacement, but it requires that name be a
primary key, and even then inserting a null value does not insert the
default value, you must actually supply only non default values.
create table t1 (value integer default 55, name text primary key);
insert into t1(name) values('hello');
insert into t1(name) values('test');
insert into t1 values(66, 'other');
update t1 set value = 77 where name = 'test';
insert or replace into t1 values(null, 'hello'); --doesn't work
insert or replace into t1(name) values('hello'); --works
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
