concat problem

zuzupus

update t_directories set updated=2,
diradress = CONCAT(fk_t_directories_parent , '|',sys_PK)
where diradress = Null and fk_t_directories_parent IN (select sys_pk from t_directories_copy)

whats wrong in this query when i try to see diradress column there is no '|'

anybody plz try to figure out whats wrong in this query,will be very grateful and the table structure is like this

CREATE TABLE tr_directories_user (
sys_pk int(11) NOT NULL auto_increment,
sys_del char(1) NOT NULL default 'f',
sys_state tinyint(2) NOT NULL default '0',
sys_dlm timestamp(14) NOT NULL,
sys_klm int(11) NOT NULL default '0',
sys_doc timestamp(14) NOT NULL,
sys_koc int(11) NOT NULL default '0',
fk_t_directories int(11) NOT NULL default '0',
fk_t_user int(11) NOT NULL default '0',
has_news set('f','t') NOT NULL default 'f',
rights tinyint(2) default NULL,
PRIMARY KEY (sys_pk),
UNIQUE KEY fk_t_directories (fk_t_directories,fk_t_user),
KEY key_sys_state (sys_state),
KEY key_sys_dlm (sys_dlm),
KEY key_sys_doc (sys_doc),
KEY Key_fk_t_directories (fk_t_directories),
KEY Key_fk_t_user (fk_t_user),
KEY has_news (has_news)
) TYPE=MyISAM;

thanks in advance

tomhath

Is the WHERE clause selecting any rows to update? The table structure you posted (obviously not the one you are updating), has NOT NULL for every column.

And you are selecting "where diraddress = null". That should be "where diraddress is null", nothing is ever equal to null.

zuzupus

sorry tomi posted wrong table it is t_directories not the posted one
i have to be more clear actaully the below table is for tree structure of parent and child relationship for eg.

PARENT1 (pk_dir = 1, diradress= 1)
CHILD1 (pk_dir = 23, diradress= 1|23)
GRANDCHILD11 (pk_dir = 345, diradress= 1|23|345)
GRANDCHILD12 (pk_dir = 346, diradress= 1|23|346)
GRANDCHILD13 (pk_dir = 347, diradress= 1|23|347)
CHILD2 (pk_dir = 35, diradress = 1|35)
_GRANDCHILD21 (pk_dir = 456, diradress= 1|35|456)
GRANDCHILD22 (pk_dir = 457, diradress= 1|35|457)
CHILD3 (pk_dir = 46, diradress = 1|46)
__GRANDCHILD31 (pk_dir = 506, diradress= 1|46|506)
_____GRANDGRANDCHILD311 (pk_dir = 657, diradress= 1|46|506|657)

CREATE TABLE t_directories (
sys_pk int(11) NOT NULL auto_increment,
sys_del char(1) NOT NULL default 'f',
sys_state tinyint(2) NOT NULL default '0',
sys_dlm timestamp(14) NOT NULL,
sys_klm int(11) NOT NULL default '0',
sys_doc timestamp(14) NOT NULL,
sys_koc int(11) NOT NULL default '0',
fk_t_directories_parent int(11) NOT NULL default '0',
name varchar(50) default NULL,
sort smallint(4) NOT NULL default '0',
bild_path varchar(250) NOT NULL default '',
bild_width smallint(4) NOT NULL default '0',
bild_height smallint(4) NOT NULL default '0',
html_file varchar(250) NOT NULL default '',
info enum('f','t') NOT NULL default 't',
diradress varchar(50) default NULL,//new cloulmn
PRIMARY KEY (sys_pk),
KEY key_sys_state (sys_state),
KEY key_sys_dlm (sys_dlm),
KEY key_sys_doc (sys_doc),
KEY Key_fk_t_directories_parent (fk_t_directories_parent),
KEY Key_sort (sort)
) TYPE=MyISAM;

actaually i want to insert data in diradress column
and i update this table For the first two levels

update t_directories set diradress = sys_pk, updated = 1 where fk_t_directories_parent = Null
First create an addiditon variable 'updated' with default = 0
Then, for the top level, you could use
update t_directories set diradress = sys_pk, updated = 1 where fk_t_directories_parent = Null

then insert all updated records in a new table (t_directories_copy),

or something like that.

Then, for the second level, you can run
update t_directories set updated=2, diradress = CONCAT(fk_t_directories_parent , '|',sys_PK) where diradress = Null and fk_t_directories_parent In ('select sys_pk from t_directories_copy)

thats why i jsut want to get the value in diradress may be this is enought o clear if still not then plz have a look at attached zip

thnaks

tomhath

Change

where fk_t_directories_parent = Null

where fk_t_directories_parent is null

Comparison operators (=, <>, etc.) do not work with null

zuzupus

sorry tom still it wont works i changed the query 🙁

tomhath

Are you checking for an error message after running the query? The following works, there's nothing wrong with the CONCAT. But you are also trying to set a column "updated" which does not exist. And you are using a subquery so I assume you are using mysql 4.x (subqueries don't work on mysql 3.2x).

UPDATE t_directories SET diradress = CONCAT( fk_t_directories_parent,  '|', sys_PK )  WHERE diradress is  NULL  AND fk_t_directories_parent = 43;