CODEWITHSUNDEEP
sqlplsqloracle11g
trashgenerator
trashgenerator

Reputation: 470

PL/SQL update all records except with max value

Please help with SQL query. I've got a table:

CREATE TABLE PCDEVUSER.tabletest
(
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR2(64),
    pattern INT DEFAULT 1 NOT NULL,
    tempval INT
);

Let's pretend it was filled with values:

INSERT INTO TABLETEST (ID, NAME, PATTERN, TEMPVAL) VALUES (1, 'A', 1, 10);
INSERT INTO TABLETEST (ID, NAME, PATTERN, TEMPVAL) VALUES (2, 'A', 1, 20);
INSERT INTO TABLETEST (ID, NAME, PATTERN, TEMPVAL) VALUES (3, 'A', 2, 10);
INSERT INTO TABLETEST (ID, NAME, PATTERN, TEMPVAL) VALUES (5, 'A', 2, 20);
INSERT INTO TABLETEST (ID, NAME, PATTERN, TEMPVAL) VALUES (4, 'A', 2, 30);

And I need to update all records (grouped by pattern) with NO MAX value TEMPVALUE. So as result I have to update records with Ids (1, 3, 5). Records with IDs (2, 4) has max values in there PATTERN group.

HELP PLZ

Upvotes: 2

Views: 1107

Answers (2)

Christian Palmer
Christian Palmer

Reputation: 1302

This select statement will help you get the IDs you need : 

SELECT
 *
FROM
 (SELECT
   id
  ,name
  ,pattern
  ,tempval
  ,MAX(tempval) OVER (PARTITION BY pattern)  max_tempval
  FROM
   tabletest
 )
WHERE 1=1
AND tempval != max_tempval
;

You should be able to build an update statement around that easily enough

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Something like this:

update tabletest t
    set ????
    where t.tempval < (select max(tempval) from tabletest tt where tt.pattern = t.pattern);

It is unclear what values you want to set. The ???? is for the code that sets the values.

Upvotes: 0

Related Questions