Is there a simple way to do the following?

The database table has many records, each record has its own unique RecordID
(PK, int), some of the records can have one text field like an intenrifier
(SomeID) with the same value. A simplified schema is looking like this:

RecordID            SomeID              Action
     1                          134                   2
     2                          123                   2
     3                          1243                 2
     4                          134                   1
     5                          1ytr                    2
     6                          1fgh                   2
     7                          1243                 1
     8                          hgf                     2
     9                          b4rfg                 2

I need to assign the value '1' or '2' to the Action field so that if we
order the whole list by the RecordID and then group it by the SomeID field,
the first record in each group (with the same SomeID field) should have
Action=2, all next entries inside each group should have Action=1. All
records without duplicates should have Action=2.

I can set Action=2 to all records, it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records?

Thanks,
Just D.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
grp_id CHAR(5) NOT NULL,
action_code INTEGER DEFAULT 2 NOT NULL
  CHECK (action_code IN (1,2)));

Action=2, all next entries inside each group should have Action=1. All
records [sic] without duplicates should have Action=2. I can set
Action=2 to all records [sic], it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records [sic] ?  <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. A normalized table should not have redundant duplicates.

I am going to guess that this is what you want:

UPDATE Foobar
  SET action_code
         = CASE WHEN foo_id
            < (SELECT MAX(foo_id)
                   FROM Foobar AS F1
               WHERE F1.grp_id = Foobar.grp_id)
            THEN 1 ELSE 2 END;

Try this one:

update tbl set
Action =
    case when RecordID = (select min(RecordID) from tbl as t where
t.SomeID = tb.SomeID)
       then 2
       else 1
    end

HI,

Excellent! That's a very good idea! Thanks!

Just D.


Show quoteHide quote

Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
Question about functions
Implicit conversion from string to date