CODEWITHSUNDEEP
sqlsql-updateamazon-redshiftinner-join
pm1359
pm1359

Reputation: 632

Cannot insert a NULL value into column x using update statement in Redshift

I am trying to update several rows using output of the other query , having the condition when nombregestorevento LIKE 'karate%'. However I don't understand the reason why Redshift is throwing the error ERROR: Cannot insert a NULL value into column nombregestorevento while there is not any null in the output of any of the queries.

Here is the query:

begin;    
update lu_gestor_evento
    set nombregestorevento = (select nombregestorevento from lu_gestor_evento_pro a12 
     where a12.id_gestorevento = lu_gestor_evento.id_gestorevento)
     WHERE lu_gestor_evento.nombregestorevento LIKE 'karate%';

 commit;

I had checked both tables and I can't find any Null in any of the tables. How can I change the query to not throwing this error.

enter image description here

Upvotes: 0

Views: 411

Answers (1)

GMB
GMB

Reputation: 222402

The error does indicate that your subquery returns a null value. If there are no null values in the source table, then it means that the subquery returned no rows - which reads as a null value in the set clause.

In other words, the error happens when the id_gestorevento of target table lu_gestor_evento does not exist in source table lu_gestor_evento_pro.

Probably, you want to update matching rows only, and leave others untouched. If so, in Redshift we can use the update/from syntax:

update lu_gestor_evento e
set nombregestorevento = ep.nombregestorevento
from lu_gestor_evento_pro ep
where ep.id_gestorevento = e.id_gestorevento

Upvotes: 1

Related Questions