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?