3

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?

1
  • 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
    Commented Jan 22, 2013 at 6:29

2 Answers 2

6

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.

3
  • 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".
    – user1822
    Commented Jan 22, 2013 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
    Commented Jan 22, 2013 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>,...);
    – bcw
    Commented Jan 22, 2013 at 10:23
2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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