How to insert data into a table using SQL?

Question

Insert into the App.settings table the following values:

(99, DEFAULT, "horizontal", "2015-09-15 04:01:04")

I have a DATABASE called App with a settings table. I am trying to insert into the table but I can not seem to get it right.

My statement:

INSERT INTO App.settings
    VALUES(99, DEFAULT, "horizontal", "2015-09-15 04:01:04");

Am I doing it right? It says my answer is wrong.

mysql> DESC App.settings;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| user_id         | int(7)              | NO   |     | NULL    |       |
| email_frequency | tinyint(2) unsigned | YES  |     | NULL    |       |
| layout          | varchar(70)         | YES  |     | NULL    |       |
| updated_at      | datetime            | YES  |     | NULL    |       |
+-----------------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

In Standard SQL strings are single-quoted: (99, DEFAULT, 'horizontal', '2015-09-15 04:01:04'). Object names are double-quoted: "App"."settings" — dnoeth
– dnoeth, Commented May 27, 2018 at 20:19
What columns are in App.settings? Can you use DESC App.settings and show us the result? Also, what DBMS are you using? (MySQL, Oracle, MSSQL, MongoDB, ...) — TwiN
– TwiN, Commented May 27, 2018 at 20:19

Gordon Linoff · Accepted Answer · 2018-05-27 20:22:05Z

2

When you use insert, always list the columns in the table. Second, the default string delimiter is the single quote in SQL rather than the double quote.

So I would expect to see:

INSERT INTO App.settings (col1, col3, col4)  -- your real column names here
    VALUES (99, 'horizontal', '2015-09-15 04:01:04');

Note that col2 was removed from the INSERT and the VALUES because you seem to want a DEFAULT value. Not all databases support that syntax.

edited May 27, 2018 at 20:22

answered May 27, 2018 at 20:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TwiN Over a year ago

Why always? If the table is not subject to change, I wouldn't say that it's absolutely necessary

Gordon Linoff Over a year ago

@TwiN . . . Because people who are learning SQL should learn how to do it right. If you are experienced and know when not to use column names, then you'll ignore the advice. There is only one case where I do not list column names, and it is something of an outlier. I always list column names for non-temporary tables. I don't want changes in table structures to cause unexpected behavior in code.

RuskiCode Over a year ago

This is the statement I use: INSERT INTO App.settings VALUES (99, DEFAULT, 'horizontal', '2015-09-15 04:01:04'); The error I get is [Error]: Task 1. Expected: Insert into the App.settings table the specified values. Try again.

TwiN Over a year ago

@GordonLinoff Ah, when in code, I definitely agree with you. We should always include the name of the columns. From his example, though, I thought it didn't look like he was trying to use it in code.

RuskiCode Over a year ago

Maybe I have to use the ALTER keyword?

|

juancarlos · Accepted Answer · 2018-05-27 20:27:58Z

0

you are sure that all the fields of the tuple are there and if so, they are in the correct order if you are not entering all the fields of the tuple you should use the following form: INSERT INTO App.settings(value, config, position, date ) VALUES(99, "DEFAULT", "horizontal", "2015-09-15 04:01:04");

In the same order

this is only an example i dont know the fields names you must be change for the you are using

answered May 27, 2018 at 20:27

juancarlos

6314 silver badges10 bronze badges

Comments

TwiN · Accepted Answer · 2018-05-27 20:42:44Z

0

Like @GordonLinoff said, you should include the columns in your query. That way, you can also skip entering the DEFAULT value for email_frequency.

As for the mistake, it's most likely the double quotes that you're currently using instead of single quotes.

Try the following:

INSERT INTO App.settings (user_id, layout, updated_at) 
    VALUES (99, 'horizontal', '2015-09-15 04:01:04');

edited May 27, 2018 at 20:42

answered May 27, 2018 at 20:35

TwiN

3,8742 gold badges22 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to insert data into a table using SQL?

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related