Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tired to execute an insert directly from phpMyAdmin as follows:

INSERT INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                ORDER
            )
VALUES(TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0)

The SQL executed successfully, however I found that all the inserted values became NULL, including the system generated values like NOW().

Does anybody have an idea?

share|improve this question
    
please post the create-statement of the table (using phpmyadmin's export-tab). Also avoid using reserved words like "order" as column name without passing it inside backticks, as this could lead to unwanted behaviour. And last: You use UPDATE-syntax for an INSERT. Look at the MySQL-Manual for how to write the VALUES-part of an INSERT. –  32bitfloat Jan 22 '13 at 6:29
add comment

2 Answers

up vote 6 down vote accepted

Your insert syntax is wrong, using column=value in the values clause doesn't do what you think it does.

Try:

INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                `ORDER`
            )
VALUES(56919,
       115948,
       '1',
       15217,
       NOW(),
       15217,
       NOW(),
       0)

The values must be put in the same order as the column names appear in the insert statement.

It would probably be a good idea to quote the order column, since that's a reserved word.

I'm guessing the nulls come from the fact that the columns aren't bound to anything in the values clause, causing them to be null. And null = any value evaluates to null:

mysql> select null=1 ;
+--------+
| null=1 |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

But that's just a guess about what is actually happening, I'm surprised that parsed at all.

share|improve this answer
    
Thanks, I will be careful next time. –  benccw Jan 22 '13 at 7:49
    
I'm really suprised that MySQL didn't reject the original syntax. But it's probably again one of those things where MySQL tries to be clever and assumes it knows what the user "intended". –  a_horse_with_no_name Jan 22 '13 at 8:22
    
@a_horse_with_no_name: with table foo(a int, b int), the following works on MySQL: insert into foo(a,b) values (42,a) and inserts (42,42). I guess that makes some kind of sense. –  Mat Jan 22 '13 at 8:48
    
I was also surprised that the parser didnt blame me. I mixed up the syntax of INSERT INTO <table> SET <COL> = <value>... with INSERT INTO <table> VALUE( <value>,...); –  benccw Jan 22 '13 at 10:23
add comment

In MySQL you can use the following syntax:

INSERT INTO oracle.PLAYLIST_MUSIC 
SET
       TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.