Update table with value from the same table using a sub query

Question

I using sqlite and have a table that looks like:

my table

I am trying to update refer column with values from col2 corresponding col1

I tried query like

update tab1 
set refer = (select col2 from tab1 where col1 = refer) 
where col1 = 2

This but is not working.

I have also tried

update tab1 
set refer = (select tem1.col2 
             from tab1 tem1, tab1 tem2 
             where tem1.col1 = tem2.refer and tem2.col1=2) 
where col1 = 2

This works.

But I am not sure whether this is the correct way to do.

Expected

Possible duplicate of [UPDATE rows with values from the same table](https://stackoverflow.com/questions/13504406/update-rows-with-values-from-the-same-table) — A. Bandtock, Mar 28 '18 at 09:06
Which dbms are you actually using? (Remove the tags for the products not involved.) — jarlh, Mar 28 '18 at 09:08
Most people here want formatted text, not images (or links to images.) — jarlh, Mar 28 '18 at 09:08

score 0 · Accepted Answer · answered Mar 28 '18 at 09:18

0

Looking to your code seesm tha you need

   update tab1 
   set refer = col2  
   when col1 = refer 
   and col1 = 2

that mean

   update tab1 
   set refer = col2  
   when  refer  2

oherwise fi yoru are looking for an update on the same table with subquery you should could use an inner join

in mysql

 update tab1 
 INNER JOIN (
      select col1, col2 
      from tab1 
      where col1 = refer ) t t.col1 = tabl1.col1 and  col1 = 2

in sqllite you could use

  update tab1 
  set refer = (select t.col2 from (
  select col2 from tab1 where col1 = refer
  ) t )
  where col1 = 2

answered Mar 28 '18 at 09:18

ScaisEdge

131,976
10
91
107

sorry got one brace missing.It works, how different is it from the one I have used and which is better? – Suresh Mar 28 '18 at 09:28
It works, how different is it from the one I have used and which is better? – Suresh Mar 28 '18 at 09:30
the different is based on the fact that the two inner subselect force the db engine to create a temp table and so the update don't work on the same .. table ..which is better ? ,,mine is more clear .. you are using unuseful cartesian product for obtain the same .. but for large table your produce performance problems – ScaisEdge Mar 28 '18 at 09:33
the same query is not working with `where col1=5` it is updating as Null, can you help @scaisEdge – Suresh Mar 28 '18 at 10:42
I think it is not working sometimes it is updating with null value. @scaisEdge – Suresh Mar 28 '18 at 10:49
for col1 =5 the refer is 2 so it should be replaced by b `update tab1 set refer = (select t.col2 from ( select col2 from tab1 where col1 = refer ) t ) where col1 = 5` – Suresh Mar 28 '18 at 11:07
where as this query woks fine all the times `update tab1 set refer = (select tem1.col2 from tab1 tem1, tab1 tem2 where tem1.col1 = tem2.refer and tem2.col1=5) where col1 = 5` – Suresh Mar 28 '18 at 11:12

score 0 · Answer 2 · answered Apr 05 '18 at 05:36

0

Finally this query works perfectly:

update tab1 
set refer = (select t1.col2 from tab1 as t1 where t1.col1 = tab1.refer) 
where tab1.col1 = 2

answered Apr 05 '18 at 05:36

Suresh

25
5

Update table with value from the same table using a sub query

2 Answers2