Thank you Carlos. It´s a really small table, it must hold less than
200 records.
regards,
JP
On 21/09/2006, at 16:09, Carlos M wrote:
On Sep 21, 2006 8:16 AM, Norman Palardy wrote:
<snip>
actually coalesce is probably the wrong function and you
may need something like
UPDATE musicians
SET leadguitarist = case musicianid = xxxx
when true then true
else false
end
WHERE .. whatever your criteria are for identifying the
members of the band ...
or something along those lines
like this (works)
create table musician (leadsinger boolean, id integer
primary key)
insert into musician (leadsinger , id ) values ( 0, 1 )
insert into musician (leadsinger , id ) values ( 0, 2 )
insert into musician (leadsinger , id ) values ( 0, 3 )
update musician set leadsinger = case id = 2 when 1
then 1 else 0 end
(note the use of 0 and 1 as false and true)
As the poster (Juan) didn't say how big is the database and how often
the update is done, I'll give other solution as if the table to update
had millions of records, that I think will be faster than using case.
Picking Norman's example I wouldn't use a boolean column with RBSQL
database - I prefer to use an integer and use 1 for True and 0 for
false (like Norman's did when inserting the records).
CREATE TABLE musician (
id INTEGER PRIMARY KEY,
leadsinger INTEGER NOT NULL DEFAULT 0
)
Then I would create an index for these two columns:
CREATE INDEX IDX_id_leadsinger ON musician (id,leadsinger)
[do the inserts or records]
To update the records I would use two update statements:
UPDATE musician SET leadsinger = 0
WHERE leadsinger = 1
(this updates only the records that have true (1) to false (0).)
UPDATE musician SET leadsinger = 1
WHERE ID = 2
(this would update the leadsinger 2 to true(1).)
Because there's an index for both columns, the updates will be faster.
Doing selects like "SELECT id FROM musician WHERE leadsinger = 1"
would be also faster due to the index.
If not using an index, the updates and selects would have to do a
table scan (search all records) and in big tables is not good. In
small tables, sometimes there's no advantage on using indexes.
Carlos
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>
