...

View Full Version : collum = " "



Trki
01-11-2012, 04:02 PM
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_
01-11-2012, 04:21 PM
What field type is cookies?

guelphdad
01-11-2012, 04:55 PM
= 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
01-11-2012, 05:05 PM
What field type is cookies?

i said varchar :)

Trki
01-11-2012, 05:06 PM
= 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
01-11-2012, 09:00 PM
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
01-11-2012, 10:36 PM
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 :D

guelphdad
01-11-2012, 11:31 PM
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
01-11-2012, 11:44 PM
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
01-12-2012, 12:14 AM
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.


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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum