CODEWITHSUNDEEP
databaseoracle-databasepostgresqlsyntax
Jason
Jason

Reputation: 794

Postgres to Oracle translation

How can I get this Postgres statement to parse into Oracle 11g?

UPDATE teams as new 
  SET counter=old.counter, 
      task_counter=old.task_counter 
FROM teams as old
WHERE new.is_old=0 
  AND old.is_old=1 
  AND new.name=old.name 
  AND new.county=old.county;

Thanks in advance.

Upvotes: 0

Views: 136

Answers (3)

David Aldridge
David Aldridge

Reputation: 52336

One handy Oracle technique is to use a MERGE for these tricky updates.

Merge into teams new 
From (
  Select counter,
         task_counter
  From   teams 
  Where  is_old = 1) old
On (new.is_old = 0          and
    new.name   = old.name   and
    new.county = old.county   )
when matched then update
set counter      = old.counter and
    task_counter = old.task_counter

Upvotes: 0

user330315
user330315

Reputation: 

UPDATE teams
  SET (counter, task_counter) = (select counter, task_counter 
                                 FROM teams old
                                 WHERE old.is_old = 1 
                                   AND teams.name = old.name   
                                   AND teams.county = old.county)
where is_old = 0

This assumes that the sub-select will only return a single row for each name/county/is_old combination.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324375

It appears that you want to execute an update with a join, ie UPDATE ... FROM. This doesn't seem to be supported directly, but can be done using UPDATE with a subquery.

See:

Upvotes: 1

Related Questions