collum = " "

[Trki]

Hi guys ... i need help ... i want do update to a empty collum. Do you know how? its posible? i tried cookies='null' or cookies=null or NULL,NULL but ... nothing :P

the cookies collum is a varchar btw.

UPDATE pocitadlo_online SET timestamp='6' WHERE ip='44.444.44.444' AND cookies=' '

[_Aerospace_Eng_]

What field type is cookies?

[guelphdad]

= NULL would be incorrect. you need to test for cookies IS NULL if it is actually a null value in the column. (I.e. not spaces which you are testing for currently with cookies = ' ')

[Trki]

Originally Posted by _Aerospace_Eng_

What field type is cookies?

i said varchar

[Trki]

Originally Posted by guelphdad

= NULL would be incorrect. you need to test for cookies IS NULL if it is actually a null value in the column. (I.e. not spaces which you are testing for currently with cookies = ' ')

hm... ah sorry i have no idea what did you say :P could you please say it more straightly ? :P or edit my code

[Old Pedant]

Here's what I would do:

UPDATE pocitadlo_online 
SET timestamp='6' 
WHERE ip='44.444.44.444' AND TRIM(IFNULL(cookies,'')) = ''

That should cover all possibilities:

-- NULL
-- ''   
-- '          '

That is, blank strings *AND* strings containing nothing but space characters *AND* NULL values will all be treated the same.

[Trki]

Originally Posted by Old Pedant

Here's what I would do:

UPDATE pocitadlo_online 
SET timestamp='6' 
WHERE ip='44.444.44.444' AND TRIM(IFNULL(cookies,'')) = ''

That should cover all possibilities:

-- NULL
-- ''   
-- '          '

That is, blank strings *AND* strings containing nothing but space characters *AND* NULL values will all be treated the same.

thx that was brilliant. I again learned something. But i little be dont understand the full code i understand AND TRIM(without spaces) but... could you explain the next code ... how it means IFNULL(cookies,'')) = ''

and why it didnt works on cookies=''

it... is meaning like " if is the value of cookies -> '' equal to '' ?

*sorry for english

[guelphdad]

when you test for a value of NULL you can't use an equals sign.

SELECT * FROM yourtable WHERE yourcolumn = NULL

is incorrect

SELECT * FROM yourtable WHERE yourcolumn IS NULL

is correct. NULL does not match anything, even another NULL so you can't test with =

more likely though you were using an empty string with perhaps one or more spaces.

the TRIM takes care of spaces and the IFNULL also checks for NULL condition so both were done in that example posted by Old Pedant.

[Old Pedant]

Also, in the query you showed, you were *NOT* testing for a blank string.

You were testing for a string with ONE SPACE in it.

And that is a very very different thing.


... AND cookies=' '

See the space in there? That would *ONLY* match a field that indeed had a single space stored in it.

[Trki]

Originally Posted by guelphdad

when you test for a value of NULL you can't use an equals sign.

SELECT * FROM yourtable WHERE yourcolumn = NULL

is incorrect

SELECT * FROM yourtable WHERE yourcolumn IS NULL

is correct. NULL does not match anything, even another NULL so you can't test with =

more likely though you were using an empty string with perhaps one or more spaces.

the TRIM takes care of spaces and the IFNULL also checks for NULL condition so both were done in that example posted by Old Pedant.

Originally Posted by Old Pedant

Also, in the query you showed, you were *NOT* testing for a blank string.

You were testing for a string with ONE SPACE in it.

And that is a very very different thing.


... AND cookies=' '

See the space in there? That would *ONLY* match a field that indeed had a single space stored in it.

Ah yeah ok ... i understand. Thx guys that was a very usefull thing what you explained me right now :P Thx and... if u would have time check this please :/

http://www.codingforums.com/showthread.php?t=248566