Can I update a particular attribute of a tuple with the same attribute of another tuple of same table? If possible what should be the algorithm?

Question

Suppose I have a table with 10 records/tuples. Now I want to update an attribute of 6th record with the same attribute of 1st record, 2nd-7th, 3rd-8th, 4th-9th, 5th-10th in a go i.e. without using cursor/loop. Use of any number of temporary table is allowed. What is the strategy to do so?

Answer 1

PostgreSQL (and probably other RDBMSes) let you use self-joins in UPDATE statements just as you can in SELECT statements:

UPDATE tbl
SET attr = t2.attr
FROM tbl t2
WHERE tbl.id = t2.id + 5
AND tbl.id >= 6

Answer 2

This would be easy with an update-with-join but Oracle doesn't do that and the closest substitute can be very tricky to get to work. Here is the easiest way. It involves a subquery to get the new value and a correlated subquery in the where clause. It looks complicated but the set subquery should be self-explanatory.

The where subquery really only has one purpose: it connects the two tables, much as the on clause would do if we could do a join. Except that the field used from the main table (the one being updated) must be a key field. As it turns out, with the self "join" being performed below, they are both the same field, but it is required.

Add to the where clause other restraining criteria, as shown.

update  Tuples t1
    set t1.Attr =(
        select  t2.Attr
        from    Tuples  t2
        where   t2.Attr = t1.Attr - 5 )
where   exists(
        select  t2.KeyVal
        from    Tuples  t2
        where   t1.KeyVal = t2.KeyVal)
    and t1.Attr > 5;

SqlFiddle is pulling a hissy fit right now so here the data used:

create table Tuples(
    KeyVal  int not null primary key,
    Attr    int
);

insert into Tuples
    select  1, 1 from dual union all
    select  2, 2 from dual union all
    select  3, 3 from dual union all
    select  4, 4 from dual union all
    select  5, 5 from dual union all
    select  6, 6 from dual union all
    select  7, 7 from dual union all
    select  8, 8 from dual union all
    select  9, 9 from dual union all
    select  10, 10 from dual;

The table starts out looking like this:

KEYVAL  ATTR
------  ----
     1     1
     2     2
     3     3
     4     4
     5     5
     6     6
     7     7
     8     8
     9     9
    10    10

with this result:

KEYVAL  ATTR
------  ----
     1     1
     2     2
     3     3
     4     4
     5     5
     6     1
     7     2
     8     3
     9     4
    10     5