CODEWITHSUNDEEP
sqlpostgresql
Dennys Gonzalez
Dennys Gonzalez

Reputation: 39

Update columns with date from the same Table sql

I have this table structure 

id | value
---|----------
1  |  0.2
2  |  0.3
3  |  0.5
4  |  0.25
5  |  0.37
6  |  0.56
7  |  
8  |  
9  |

And now I want to update the value of last 3 elements with the value of the element id = between 2 and 5 I did this like this because sound very simple

UPDATE table AS z0
SET value = z1.value
FROM table as z1
WHERE z1.id BETWEEN 2 AND 5
AND z0.id BETWEEn 7 AND 9

but the result is like this, is just taking the value of the first element in the range (id=2) 

 id | value
    ---|----------
    1  |  0.2
    2  |  0.3
    3  |  0.5
    4  |  0.25
    5  |  0.37
    6  |  0.56
    7  |  0.3
    8  |  0.3
    9  |  0.3

Upvotes: 0

Views: 372

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133410

You can use this 

 UPDATE table AS z0
 SET value = z1.value
 FROM table as z1
 WHERE z1.id = (z0.id - 4)
 AND z0.id BETWEEn 7 AND 9

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Does this work?

update table 
    set value = (select value
                 from table t2
                 where id between 2 and 5
                 order by random()
                 limit 1
                )
    where value is null;

Alas, the above doesn't quite work (although I think it should). The problem is that the subquery is being executed only once. This version does work:

update table t
    set value = (select value
                 from table t2
                 where id between 2 and 5 and t2.id <> t.id
                 order by random()
                 limit 1
                )
    where value is null;

The correlation clause prevents the optimization of running the query only once. Here is a SQL Fiddle that demonstrates the logic.

Upvotes: 2

Related Questions