How to insert values in a new mysql db column without starting a new row?

Question

I have two tables in a mysql database: Table Cars and Table Comments. Table cars has columns ID which is auto incremented and name. For each name in table cars I want to have a column in table comments and post the comments from users as they come. How do I insert a new value in an empty column without creating a new row. Basically what happens is say I have this database

X _ _ 
X _ _
X _ _

Instead of posting

X _ X 
X _ _
X _ _

it is inserting the new value at

X _ _ 
X _ _
X _ _
_ _ X

And then the next one at

    X _ _ 
    X _ _
    X _ _
    _ _ X
    _ X _

I have looked at the join function but that seems to join just two columns and not a row with a column. I've also seen

SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

but it is not explained very well. How do I make this work?

I think you might be mis-understanding fundamental concepts of databasing. Why would you want it to work like this? — esqew, Jul 17 '14 at 20:20
update how, I know I need to update but how to I get where to update. — Jerome, Jul 17 '14 at 20:20
UPDATE Table_Cars SET Comment="This is a commentary" where ID=NUMERIC_ID — Ringo, Jul 17 '14 at 20:22
@esqew yes, that's why I am asking, if I knew how it's supposed to be done I wouldn't be here asking. — Jerome, Jul 17 '14 at 20:22

score 5 · Accepted Answer · answered Jul 17 '14 at 20:20

5

Your schema, as described, doesn't make any sense. If you want to have multiple Comments per Car, add a foreign key on Comment to refer to the Car it's talking about.

Car (Id, Name)
Comment (Id, CarId, CommentText)

Then, whenever a Comment comes in,

INSERT INTO Comment (CarId, CommentText) VALUES (@CarId, @CommentText)

answered Jul 17 '14 at 20:20

Jacob Krall

28,341
6
66
76

I used this to create the foreign key: http://stackoverflow.com/questions/459312/setting-up-foreign-keys-in-phpmyadmin – Jerome Jul 18 '14 at 07:40

Cindy Langdon · Answer 2 · 2014-07-17T20:25:03.733

What you need is to find the row you are selecting and then doing an update, instead of doing an insert.

You can also achieve this by using a trigger. Let me know if this is what you want to do and I will expand on my post.

This page will tell you what UPDATE is: http://dev.mysql.com/doc/refman/5.0/en/update.html

If the row exists, then do an update, otherwise do an insert. This is a sample code. You have to customize it according to your table names:

INSERT INTO CarComment (col1, col2)
VALUES ("value1","value2")
ON DUPLICATE KEY UPDATE 
  value1=VALUES(something)

The explanation of the VALUES() is this: http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_values

How to insert values in a new mysql db column without starting a new row?

2 Answers2