CODEWITHSUNDEEP
sqlamazon-redshift
Chubaka
Chubaka

Reputation: 3155

redshift: update a Null string into NULL

There is a redshift table with a "column_a"

  column_a                       varchar(1000) null,

the following query will return a lot of results in 'Null' strings

select column_a from table where lower(column_a)='Null' limit 100;

but if i do

update table set column_a = NULL where lower(column_a)='';

i got an error message

Cannot insert a NULL value into column binhash

is there anyway i can replace the 'Null' string into NULL? Thanks

Upvotes: 0

Views: 5164

Answers (1)

Golokesh Patra
Golokesh Patra

Reputation: 598

Please correct me If I haven't understood your question.

AS PER MY UNDERSTANDING

You have a table like this -

create table dev.gp_test_20200731
(
name varchar(100) NOT NULL,
rolnum int ,
subject varchar(100) null
);

Data Like this -

enter image description here

i.e inserted this way -

('abc',01),
('cde',02,'chemistry'),
('def',03,'')

So , rolnum = 1, has subject column as NULL -

select * from dev.gp_test_20200731 where subject is null

enter image description here

& rolnum = 3, has subject column as Empty String or ''-

select * from dev.gp_test_20200731 where subject = ''

enter image description here

Now I am running the update query on rolnum=3 which is having subject column as empty string.or in your case it will be a string 'NULL' -

update dev.gp_test_20200731 set subject = null  where lower(subject) = ''

Result -

select * from dev.gp_test_20200731 where subject is null

enter image description here

I am unable to reproduce this issue.

Upvotes: 1

Related Questions