peterwinson1 wrote:
> Hello,
>
> I have a some what complex question about UPDATE. I have the following
> table
>
> table1 (KEY, COL1)
>
> 0, 1
> 1, 2
> 2, 3
> 3, 4
>
> What I would like to do is to UPDATE COL1 by subtracting the COL1 value
> where KEY = 0 from the COL1 value of the current row so that the result
> would be.
>
> 0, 0
> 1, 1
> 2, 2
> 3, 3
>
> Can this be done in SQL? It does not have to be one UPDATE/SELECT
> statement.
Not sure if it's optimal, but it works:
update table1 set col1=col1-(SELECT col1 FROM table1 WHERE key=0);
The full session:
[hudson:~] $ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table table1(key integer primary key, col1 integer);
sqlite> insert into table1 values(0,1);
sqlite> insert into table1 values(1,2);
sqlite> insert into table1 values(2,3);
sqlite> insert into table1 values(3,4);
sqlite> select * from table1;
0|1
1|2
2|3
3|4
sqlite> begin;
sqlite> update table1 set col1=col1-(SELECT col1 FROM table1 WHERE key=0);
sqlite> select * from table1;
0|0
1|1
2|2
3|3
Eric
--
Eric A. Smith
(1) Auto da fe (2) Beating with clubs (3) Beheading: Decapitation
(4) Blowing from cannon (5) Boiling (6) Breaking on the wheel (7) Burning
(8) Burying alive (9) Crucifixion (10) Decimation (11) Dichotomy
(12) Dismemberment (13) Drowning (14) Exposure to wild beasts etc.
(15) Flaying alive (16) Flogging: Knout (17) Garrote (18) Guillotine
(19) Hanging (20) Hari kari (21) Impalement (22) Iron Maiden
(23) Peine Forte et Dure (24) Poisoning (25) Pounding in mortar
(26) Precipitation (27) Pressing to death (28) Rack
(29) Running the gauntlet (30) Shooting (31) Stabbing (32) Stoning
(33) Strangling (34) Suffocation.
-- List of execution methods compiled by the New York State
Commission to Investigate and Report the Most Humane and
Practical Methods of Carrying into Effect the Sentence
of Death, 1888
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
