insert mysql后加where,如何在MySQL Insert語句中添加where子句？

This doesn't work:

這不起作用:

INSERT INTO users (username, password) VALUES ("Jack","123") WHERE id='1';

Any ideas how to narrow insertion to a particular row by id?

任何想法如何通過id縮小插入到特定行?

8 个解决方案

#1

20

In an insert statement you wouldn't have an existing row to do a where claues on? You are inserting a new row, did you mean to do an update statment?

在一個插入語句中,你不會有一個現有的行來做一個claues on?你正在插入一個新行,你的意思是做一個更新聲明嗎?

update users set username='JACK' and password='123' WHERE id='1';

#2

8

A conditional insert for use typically in a MySQL script would be:

通常在MySQL腳本中使用的條件插入是:

insert into t1(col1,col2,col3,...)

select val1,val2,val3,...

from dual

where [conditional predicate];

You need to use dummy table dual.

你需要使用虛擬表雙。

In this example, only the second insert-statement will actually insert data into the table:

在此示例中,只有第二個insert-statement實際上會將數據插入表中:

create table t1(col1 int);

insert into t1(col1) select 1 from dual where 1=0;

insert into t1(col1) select 2 from dual where 1=1;

select * from t1;

+------+

| col1 |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

#3

3

To add a WHERE clause inside an INSERT statement simply;

要在INSERT語句中添加WHERE子句,只需簡單;

INSERT INTO table_name (column1,column2,column3)

SELECT column1, column2, column3 FROM table_name

WHERE column1 = 'some_value'

#4

2

Try this:

Update users

Set username = 'Jack', password='123'

Where ID = '1'

Or if you're actually trying to insert:

或者如果你真的想插入:

Insert Into users (id, username, password) VALUES ('1', 'Jack','123');

#5

1

I think you are looking for UPDATE and not insert?

我認為你正在尋找UPDATE而不是插入?

UPDATE `users`

SET `username` = 'Jack', `password` = '123'

WHERE `id` = 1

#6

0

INSERT INTO users (id,username, password)

VALUES ('1','Jack','123')

ON DUPLICATE KEY UPDATE username='Jack',password='123'

This will work only if the id field is unique/pk (not composite PK though) Also, this will insert if no id of value 1 is found and update otherwise the record with id 1 if it does exists.

僅當id字段是唯一的/ pk(不是復合PK)時,這將起作用。此外,如果沒有找到值1的id,則將插入,否則如果存在,則更新id為1的記錄。

#7

0

UPDATE users SET username='&username', password='&password' where id='&id'

This query will ask you to enter the username,password and id dynamically

此查詢將要求您動態輸入用戶名,密碼和ID

#8

0

For Empty row how we can insert values on where clause

對於Empty行,我們如何在where子句中插入值

Try this

UPDATE table_name SET username="",password="" WHERE id =""