UPDATE query updates all the data-CodePudding

There is something that I do not understand, to see if someone could help me.

How is it possible that in a statement like the one I indicate updates all the data of a field, even having an inner clause?

update temp_sq05 
set tipoproynew =(
  SELECT
    temp_sq06.TP
  FROM
    temp_sq05 ts   
  INNER JOIN temp_sq06  
    ON temp_sq05.te_cod = temp_sq06.tecodmg 
    and temp_sq05.tipoproynew IS NULL
)

CodePudding user response：

The subquery that you use to update the column tipoproynew should be correlated to the table temp_sq05 and this can't be done with the join.
Try this:

UPDATE temp_sq05 AS t5  
SET tipoproynew = (
  SELECT t6.TP
  FROM temp_sq06 AS t6  
  WHERE t6.tecodmg = t5.te_cod  
)
WHERE t5.tipoproynew IS NULL;

If your version of SQLite is 3.33.0 you could use the UPDATE...FROM... syntax:

UPDATE temp_sq05 AS t5 
SET tipoproynew = t6.TP
FROM temp_sq06 AS t6  
WHERE t5.te_cod = t6.tecodmg AND t5.tipoproynew IS NULL;