Alternative way of an INSERT with WHERE clause?

[savedlema]

Hi!
I'm programming with VB.NET + Mysql database. I have one table of which, one of its column can have multiple entries to it. That's, say a table named TableA(item2,item2,item3,item4). Now, I have a scenario where I may have item1,item2 & item4 filled,but item3 is not. (Please have a look at the illustration screenshot attached and see what I filled in the "Favorite Color" for the person with id= 6, I want to do it for the person with id=8)

Then I want to enter data into ONLY item3 (which was empty). A common way would be using UPDATE statement, but an update will not help me in my case, since more than one data will need to be entered in that specific item4 (which will have to be identified by a WHERE id=xx). UPDATE will not help because as far as I know, UPDATE will like delete what is in that item and update it with the new entry.

What I want is, if there're already some entries in item3 (and for a specific id) say, there is blue & red in it, I want to be able to add green,black,orange....to it.

The first thing that came to mind was using INSERT INTO Table1 (color) VALUES (green) WHERE id = XX, but I realize INSERT does not go with WHERE clause.

Does someone out there have an idea of what I could use to accomplish the same thing? To enter some entries to a certain item identified by an id?

[cereal]

Hi!

You can use concat_ws() to add content to a string, for example:

update table1 set color = concat_ws(',', color, 'green') where id = XX;

Full example:

> create table table1 (
    id int unsigned auto_increment primary key not null,
    color varchar(255) not null,
    food varchar(255) not null);

> insert into table1 (id, color, food) values(1, 'red,green','rice');

> select * from table1;
+----+-----------+------+
| id | color     | food |
+----+-----------+------+
|  1 | red,green | rice |
+----+-----------+------+
1 row in set (0.00 sec)

> update table1 set color = concat_ws(',', color, 'blue') where id = 1;

> select * from table1;
+----+----------------+------+
| id | color          | food |
+----+----------------+------+
|  1 | red,green,blue | rice |
+----+----------------+------+
1 row in set (0.00 sec)

Regarding the insert query, this can support a subquery in which you can create the conditional statements, but in this case this is not the correct solution to update an existing value, because it will always add a new row, for example:

> insert into table1 (color) select concat_ws(',', color, 'orange') as color from table1 where id = 1;

> select * from table1;
+----+-----------------------+------+
| id | color                 | food |
+----+-----------------------+------+
|  1 | red,green,blue        | rice |
|  2 | red,green,blue,orange |      |
+----+-----------------------+------+
2 rows in set (0.00 sec)

And since the id column is a primary key, if you remove the auto_increment it will still produce an error for duplicate key.

The alternative is to use the INSERT ... ON DUPLICATE KEY UPDATE syntax:

• http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
• http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat-ws
• http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

[JorgeM]

Sounds to me like you would need break up the table into multiple tables

personId    name    
1           Jim
2           Sally
3           John
4           Fred
5           Sam
6           Marie
7           Fred
8           Tom

favid   personId    favoriteColor
1       5           red
2       6           green
3       6           orange
4       6           purple


foodId  personId    food
1       5           rice
2       6           fish
3       8           salad

Then build your queires to include appropriate joins.

[savedlema]

Cereal..
Thank you very much. I have found help in your reply. I had never met concat_ws anywhere, it has worked for me and now I'm ready to move on.

Thank you very much for taking your time to explain with all those examples. I know it took some of your precious time.

I appreciate.

[savedlema]

Cereal, is there a way I can make every new entry go to a second line?
Instead of [blue,green,yellow] to make them appear like:
blue
green
yellow
Is this possible?

[cereal]

Yes, you can use the linefeed character \n:

update table1 set color = concat_ws('\n', color, 'lime') where id = XX;

But from a usable point of view, a comma separated list is easier to manipulate, for example in PHP with explode() to get an array or with str_replace() to get a string:

print_r(explode(',' $row['color']);
echo str_replace(',', '<br />', $row['color']);

And you can also use the FIND_IN_SET() function of MySQL:

• http://dev.mysql.com/doc/refman/5.5/en/string-literals.html
• http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

Consider also JorgeM post, which is a far more flexible solution. Bye!

[savedlema]

Thanks a lot friends.
Solved.