SQLite Forum

select distinct in table and modify column
Login
Chronological

select distinct in table and modify column

(1) By anonymous on 2021-12-27 19:49:52 [link] [source]

I have this table called qsos which has around 1700 rows of data.

timestamp = 2021-08-01 14:20:27 call = ND9E txfreq = 144000.0 rxfreq = 144000.0 mode = FM contestname = QSOPARTY snt = 59 rcv = 59 countryprefix = K stationprefix = W0W qth = name = comment = PUT rcvnr = 0 section = MO prec = ck = 0 zone = 4 sentnr = 1"039 points = 1 ismultiplier1 = 0 ismultiplier2 = 0 power = band = 144.0 wpxprefix = ND9 exchange1 = PUT radionr = 1 contestnr = 1 ismultiplier3 = 0 misctext = isrunqso = 0 mycall = run1run2 = 1 gridsquare = operator = W0JSG continent = NA RoverLocation = ADR RadioInterfaced = 1 NetworkedCompNr = 0 NetBiosName = W0JSG-LT1 IsOriginal = 0 ID = f3487e1e96744bf5a8da578bac0f74d3 IsClaimedQso = 1

I'd like to do a couple of things.

  1. where ismultiplier1 = 1, change that to 0 for all rows. That column can only be a 1 or a 0. Therefore the value for all the rows should be 0. I think that sql command is as follows;

update qsos set ismultiplier1='0' where ismultiplier1='1';

  1. exchange1 can be any combo of 112 different values over the 1700 records. For the first distinct instance of exhcange1, I'd like to change the ismultiplier1 field to a 1 in the table.

Thanks Kyle

(2) By Igor Tandetnik (itandetnik) on 2021-12-28 01:01:14 in reply to 1 [source]

Something along these lines perhaps (not tested):

update qsos set ismultiplier1 =
case when exists (
  select 1 from qsos t2
  where t2.exchange1=qsos.exchange1
    and t2.timestamp < qsos.timestamp)
then 0 else 1 end;

Here I assume that by "first distinct instance" you mean first in chronological order by timestamp.

(5) By anonymous on 2021-12-28 23:15:39 in reply to 2 [link] [source]

This worked! Thank you! It would of taken me days of research and tons of trial and error before I gotten this to work.

Kyle

(3) By Keith Medcalf (kmedcalf) on 2021-12-28 04:58:40 in reply to 1 [link] [source]

Or, assuming that you have a unique index on (exchange1, timestamp) mayhaps:

with qsosnew(exchange1, timestamp, ismultiplier1)
  as (
      select exchange1,
             timestamp,
             (count(*) over (partition by exchange1 order by timestamp rows between unbounded preceding and current row)) == 1
        from qsos
     )
update qsos
   set ismultiplier1 = qsosnew.ismultiplier1
  from qsosnew
 where qsos.exchange1 == qsosnew.exchange1
   and qsos.timestamp == qsosnew.timestamp
   and qsos.ismultiplier1 != qsosnew.ismultiplier1
;

(4) By anonymous on 2021-12-28 13:24:59 in reply to 3 [link] [source]

Exchange is not unique. Here is the schema for the table.

CREATE TABLE qsos( "timestamp" TEXT, "call" TEXT, "txfreq" TEXT, "rxfreq" TEXT, "mode" TEXT, "contestname" TEXT, "snt" TEXT, "rcv" TEXT, "countryprefix" TEXT, "stationprefix" TEXT, "qth" TEXT, "name" TEXT, "comment" TEXT, "rcvnr" TEXT, "section" TEXT, "prec" TEXT, "ck" TEXT, "zone" TEXT, "sentnr" TEXT, "points" TEXT, "ismultiplier1" TEXT, "ismultiplier2" TEXT, "power" TEXT, "band" TEXT, "wpxprefix" TEXT, "exchange1" TEXT, "radionr" TEXT, "contestnr" TEXT, "ismultiplier3" TEXT, "misctext" TEXT, "isrunqso" TEXT, "mycall" TEXT, "run1run2" TEXT, "gridsquare" TEXT, "operator" TEXT, "continent" TEXT, "RoverLocation" TEXT, "RadioInterfaced" TEXT, "NetworkedCompNr" TEXT, "NetBiosName" TEXT, "IsOriginal" TEXT, "ID" TEXT type UNIQUE, "IsClaimedQso" TEXT );