Jump to content
Larry Ullman's Book Forums

Recommended Posts

Regarding to the words table, I would like to play around by adding one (or more columns) with respective values into it by applying these two blocks of code:

 

Code 1:

// Add a new column:

 

$query = 'ALTER TABLE words
             ADD COLUMN newcol VARCHAR(255) NOT NULL
            ';

 

And then I went to add values for it which must match the lang_id unique column, right?

 

Code 2:

 

// insert values into it:

 

I followed the syntax sample as shown in the book, I applied this one:

 

$query = "INSERT INTO words (lang_id, newcol) VALUES (1, 'newvalue1'), (2, 'newvalue2')";

 

As you can see that the lang_id is the UNIQUE indexed one, so I did like this:

 

$query = "INSERT INTO words (lang_id, newcol) VALUES (1, 'newvalue1'), (2, 'newvalue2');

 

ON DUPLICATE KEY UPDATE newcol = 'newvalue1, newvalue2' ";

 

It worked fine to me.

 

However, when I viewed the result table, the two records/rows for the newcol column were updated with the newvalue2 only.

 

So, the question is how can I insert the two new values into this single new column which should go exactly into the respective lang_id, 1 and 2, as it should be?

 

Your answers will be very much appreciated!

 

Note: ON DUPLICATE KEY UPDATE is not mentioned in any original codes in the book, nor does the book mention about it. I've searched around about it. So, it's not the perfect one, i guessed.

Link to comment
Share on other sites

Hi Eric,

I'm a newb just doing research on issues raised in this forum. As you said, ON DUPLICATE KEY is not mentioned in the book, so I thought I would add some useful information here about it. If it helps you with your problem great, otherwise this is just for those of us who aren't yet familiar with that particular statement.

 

ON DUPLICATE KEY checks if the data you're inserting violates a unique key requirement, turning into an UPDATE on the row which has the key combination that caused the violation. 

For example:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3; 

If your primary key is only one of the fields (e.g. 'a'), and you already had a row in the table where a=1, then you'd get an UPDATE of that existing row that results in the 'c' value being set to 3.

 

If it's a composite key (say, 'a,b'), then if you had existing records with a=1 and b=2, then that rows' C would get changed to 3 instead of a new record being created.

 

If there's no unique/primary keys on this table, then you'd never get an update, it'd just be an extra-verbose INSERT statement.

 

-http://stackoverflow.com/questions/8610269/mysql-on-duplicate-key-update

also see, MySQL Reference Manual

 

  • NOTE: As a refresher, UNIQUE indexes require that each row have a unique value for that column. UNIQUE indexes are added to any columns, here lang_id, whose values cannot be duplicated within the table (Ullman 179-180).
  • Upvote 1
Link to comment
Share on other sites

@ indigetal: Your reply is appreciated. However, what i'd like to mention (and also discuss) here is that how to insert two values/ rows into one single column of the table that companions with the UNIQUELY indexed column, lang_id?

 

P/S: I also tried the following syntax:

$query = "INSERT INTO words (lang_id, newcol) VALUES (1, 'newvalue1'), (2, 'newvalue2')

 

ON DUPLICATE KEY UPDATE newcol= VALUES(('newvalue1'),('newvalue2'))

";

 

But it failed :-( and returned the message like this:

 

Could not insert into the table because:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('newvalue1'),('newvalue2')
at line 5.

Link to comment
Share on other sites

I'm kind of confused here. lang_id is the UNIQUE column, and you're providing values 1, 2 there, which should suggest no problem. But you're trying to dynamically change the value inserted into `newcol`, which does not have a UNIQUE index? Confused...

Link to comment
Share on other sites

@Larry: as you see, when we create a new column for the words table, called newcol, its records/ rows in the order of lang_id is empty. Now, we insert values into these respective two rows, one is in the default language, English, and the another is in my language, Vietnamese.

 

1/ If I use this query:

 

$query = "INSERT INTO words(word_id, lang_id, newcol)
               VALUES
            (NULL, 1, 'english_value'),
            (NULL, 2, 'vietnamese_value')
            ";

It will insert the next row for word_id column, because word_id is the AUTO_INCREMENT column.

 

2/ because of the 1) above, I omit the word_id, and lang_id, to use this query:

 

$query = "INSERT INTO words (newcol)
               VALUES
            ('english_value'),
            ('vietnamese_value')
            ";

 

It will return syntax error like this: Could not insert into the table because: Duplicate entry '0' for key 2.

 

When I checked the result table, you know, it created the new row for word_id with the first value inserted.

 

3/ Then I try the original syntax shared above in the original post with using the ON DUPLICATE KEY UPDATE, but i failed.

 

=> finally, i got stuck there.

 

So, can you grab my idea here, and show me how to do it?

 

appreciated!

Link to comment
Share on other sites

 

Hi eric,

 

I don't know if this helps you, but I added new records into the words table using:

UPDATE words SET new_col = 'english_value.' WHERE lang_id = 1;

 

Thanks. this works well.

 

My concern is how to add the 'english_value' and 'vietnamese_value' into the rows of lang_ids at the same time.

Link to comment
Share on other sites

 Share

×
×
  • Create New...