c++ - MySQL delete all items of a person without deleting this id of the person -

this second question , trying build on this one.

so, want delete items of person without deleting id of person (so can buy new items again when chooses to).

i have 3 tables :

create table persons(personid int not null auto_increment primary key);  create table items(itemid int not null primary key);  create table personsitems(personsitemsid int not null auto_increment primary key, person_id int not null, item_id int not null, constraint fk_person_id foreign key (person_id) references persons(personid), constraint fk_item_id  foreign key (item_id) references items(itemid)); 

i fill tables.

and last table that:

insert personsitems(person_id, item_id ) values (1,5),(1,8),(1,10) 

so then, have person id=1 3 items, items id 5, 8 , 10.

i want statement delete items id of person 1. then, person has no items @ in future if wishes to, can take items again.

mysql_query(connection, \                    " delete * personsitems(personsitemsid, person_id, item_id) personid = 1");                     printf("%ld row(s) deleted!\n", (long) mysql_affected_rows(connection));                     //see remaining rows                    mysql_query(connection, \                         "select person_id, item_id personsitems");             //resource struct rows of returned data.            resource = mysql_use_result(connection);                     // fetch single result            result = mysql_fetch_row(resource);                     // display persons's new items            while((result = mysql_fetch_row(resource))) {                printf("%s %s\n",result[0],result[1]);            } 

produces

-1 row(s) deleted! 1 5 1 8 1 10  mysql> select * personsitems; +----------------+-----------+----------+ | personsitemsid | person_id | item_id  | +----------------+-----------+----------+ |          201   |       1 |        5   | |          202   |       1 |        8   | |          203   |       1 |       10   | +----------------+---------+------------+ 

so, deleted none . fields deleted without personid 1 deleted @ all.

can happen? in advance. ps: have been working on days now.

your delete query wrong, should in form delete table_name some_column = some_value (see here).

so in case be:

delete personsitems personid = 1 

update: comment, seems though want have personsitems table following after query:

+----------------+-----------+----------+ | personsitemsid | person_id | item_id  | +----------------+-----------+----------+ |                |       1   |          | |                |       1   |          | |                |       1   |          | +----------------+-----------+----------+ 

in case should use following query:

update personsitems set personsitemsid='', item_id='' person_id='1'