CODEWITHSUNDEEP
mysqlsql
DRE
DRE

Reputation: 29

SQL Error when trying to insert one value into one row for a column

I'm using MySQL 5.7 and for some reason my INSERT statement isn't working as before even though the syntax looks correct. It's error-ing out on the where statement...

SQL:

insert into users(age) values('16') where username='r';

Upvotes: 1

Views: 199

Answers (4)

arman1991
arman1991

Reputation: 1166

If you want to insert new records in your table, you have to write query for inserting data.

SQL INSERT INTO Statement syntax is this:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

In your case, if you don't have the record in your database, your query will look like this:

INSERT INTO users (username, age)
VALUES ('r', '16')

But if you want to update existing records in your table, you have to write query for updating data using the SQL UPDATE Statement.

The syntax for this is:

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

To update the record/s, you have to specify clause in WHERE which record should be modified.

To modify age of user/s with username that is equals 'r', this is the query:

UPDATE users SET age = 16 WHERE username =  'r'

but if you want to modify for all users which usernames starts with 'r':

UPDATE users SET age = 16 WHERE username =  'r%'

I hope this explanation will help you to understand better SQL statements for INSERT new and UPDATE existing records.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

That syntax isn't correct. You can't use where like that. Perhaps you want something like:

insert into users (username, age)
values ('r', '16')

Alternatively if that user already exists, you might be looking for an update statement instead:

update users set age = '16' where username = 'r'

Upvotes: 4

jpw
jpw

Reputation: 44871

If the row for username r already exists perhaps you are looking to update the age value instead?

Use: update users set age = 16 where username = 'r' instead.

Also, I'm just guessing here, but maybe age holds a numeric value, and if so you can remove the quotes around 16.

Upvotes: 5

Asaph
Asaph

Reputation: 162771

INSERT statements must not contain a WHERE clause. Remove it.

If, in fact what you are trying to do is update an existing row, use an UPDATE statement, not an INSERT statement.

update users set age = '16' where username='r';

Upvotes: 3

Related Questions