oracle - SQL - Update last occurrence with select in clause -


in oracle, update on table last occurrence based on select in list, :

update table t1 set t1.fielda = 0 t1.id in (     select t2.id, max(t2.timestamp)     table t2     t2.id in (1111,2222,33333)     group t2.id    ); 

this query not works, received error "too many values". ideas? thanks

i believe oracle supports multiple columns in, try:

update table t1  set t1.fielda = 0     (t1.id,t1.timestamp) in ( select t2.id, max(t2.timestamp) table t2 t2.id in (1111,2222,33333) group t2.id ); 

essentially if you're providing subquery right side of in has result set 2 columns, have provide names of both columns in brackets on left side of in

incidentally, i've never liked updating based on max value if requirement 1 row updated, 2 rows same max value both updated. kind of query better:

update table set fielda=0 rowid in(   select rowid (     select rowid, row_number() over(partition id order timestamp desc   ) rown table ) rown=1) 

if, however, primary key on table includes time stamp part of compound, there no concern of here being 2 rows same id/timestamp.. it's case!


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -