Re: Help with an Update Statment

Your table has no key; can I guess you meant to post this?

CREATE TABLE Foobar
(cust_id INTEGER NOT NULL,
officers_id VARCHAR(10) NOT NULL,
PRIMARY KEY (cust_id, officers_id)
officer_rank INTEGER NOT NULL);

The next question is why do you want to build a table that has to be
constantly updated when you can use a VIEW that is always right?

CREATE VIEW TopTwoOfficers (cust_id, officer_1, officer_2
AS
SELECT cust_id,
MAX (CASE WHEN ranking = 1 THEN officers_id ELSE NULL END) AS
officer_1,
MAX (CASE WHEN ranking = 2 THEN officers_id ELSE NULL END) AS
officer_2
FROM (SELECT cust_id, officers_id,
ROW_NUMBER() OVER(PARTITION BY cust_id
ORDER BY officer_rank,
officers_id)
FROM Foobar) AS Top(cust_id, officers_id, ranking)
WHERE ranking IN (1, 2)
GROUP BY cust_id;

There is no need to use the proprietary PIVOT extension or multiple
CTEs, either.
.

Relevant Pages

  • Re: Shuffling empty address columns
    ... The reason I need to 'lose' the blank lines is that the Royal Mail has told ... addresses using Access and normally I would just use 'shrink field', ... > CREATE TABLE Foobar ... > (id INTEGER NOT NULL PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: How can I get a non consecutive numbers in collum.
    ... >CREATE TABLE Foobar ... >(seq INTEGER NOT NULL PRIMARY KEY ... This doesn't seem to work on a table containing seq values 1, 2, and 3. ... >Please post DDL, so that people do not have to guess what the keys, ...
    (microsoft.public.sqlserver.programming)
  • Re: sql query question
    ... would make it a sequential file. ... specifications. ... NOT NULL PRIMARY KEY, ... FROM Foobar AS F1, Foobar AS F2 ...
    (microsoft.public.sqlserver.programming)
  • Re: Shuffling empty address columns
    ... "address2", but it shows up for commerical users. ... CREATE TABLE Foobar ... (id INTEGER NOT NULL PRIMARY KEY, ... INSERT INTO Foobar VALUES (1, '123 MainStreet', NULL); ...
    (microsoft.public.sqlserver.programming)
  • Re: Convert dense_rank and row_number sql server functions to access
    ... As well as for row_numbergoes, it is logically the same as ranking the primary key (and if you use 'partition', that ranking as to be computed "by group'). ... Vanderghast, Access MVP ...
    (microsoft.public.access.queries)