MySQL Insert Where 쿼리

MySQL Insert Where 쿼리

---

이 쿼리의 문제점 :

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

WHERE절 없이 작동합니다 . SQL을 잊은 것 같네요 ..

---

MySQL INSERT 구문 은 WHERE 절을 지원하지 않으므로 쿼리가 실패합니다. id열이 고유하거나 기본 키 라고 가정합니다 .

ID 1로 새 행을 삽입하려는 경우 다음을 사용해야합니다.

INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

ID가 1 인 기존 행의 weight / desiredWeight 값을 변경하려는 경우 다음을 사용해야합니다.

UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;

원하는 경우 INSERT .. ON DUPLICATE KEY 구문을 다음과 같이 사용할 수도 있습니다.

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

또는 그렇게 :

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

또한 id열이 자동 증가 열이면 INSERT에서 모두 생략하고 mysql이 정상적으로 증가하도록 할 수 있다는 점에 유의하는 것도 중요합니다 .

---

WHERE 절을 VALUES 절과 결합 할 수 없습니다. 내가 아는 한 두 가지 옵션이 있습니다.

1. INSERT 지정 값

   INSERT INTO Users(weight, desiredWeight) 
   VALUES (160,145)
   

2. SELECT 문을 사용하여 INSERT

   INSERT INTO Users(weight, desiredWeight) 
   SELECT weight, desiredWeight 
   FROM AnotherTable 
   WHERE id = 1
   

---

UPDATE 쿼리에 WHERE 절을 사용합니다. INSERT 할 때 행이 존재하지 않는다고 가정합니다.

OP의 진술은 다음과 같습니다.

UPDATE 사용자 SET weight = 160, desiredWeight = 45 여기서 id = 1;

MySQL에서 INSERT 또는 UPDATE를 원할 경우 WHERE 절과 함께 REPLACE 쿼리를 사용할 수 있습니다. WHERE가 존재하지 않으면 INSERTS하고 그렇지 않으면 UPDATES합니다.

편집하다

나는 Bill Karwin의 요점이 코멘트에서 빠져 나와 그것을 매우 분명하게 만들기에 충분히 중요하다고 생각합니다. 감사합니다 Bill, MySQL로 작업 한 지 너무 오래되었습니다. REPLACE에 문제가 있다는 것을 기억했지만 그게 뭔지 잊어 버렸습니다. 내가 찾아 봤어야했는데.

그것은 MySQL의 REPLACE가 작동하는 방식이 아닙니다. DELETE (행이없는 경우 no-op 일 수 있음)와 INSERT를 수행합니다. 결과에 대해 생각해보십시오. 트리거 및 외래 키 종속성. 대신 INSERT ... ON DUPLICATE KEY UPDATE를 사용하십시오.

---

삽입에 WHERE 절이 있다고 생각하지 않습니다.

---

검색어 삽입은 where 키워드 *를 지원하지 않습니다.

하위 선택 문에 where 조건을 사용할 수 있으므로 조건이 적용됩니다. 하위 선택을 사용하여 복잡한 삽입을 수행 할 수 있습니다.

예를 들면 :

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

insert 문에 "select"를 입력하면 다중 삽입을 빠르게 수행 할 수 있습니다.

이 유형의 삽입을 사용하면 삽입되는 행 수를 확인할 수 있습니다. 삽입을 수행하기 전에 다음 SQL 문을 실행하여 삽입 될 행 수를 결정할 수 있습니다.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

EXISTS 조건을 사용하여 중복 정보를 삽입하지 않도록 할 수 있습니다.

예를 들어 기본 키가 client_id 인 clients라는 테이블이있는 경우 다음 문을 사용할 수 있습니다.

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

이 문은 하위 선택으로 여러 레코드를 삽입합니다.

단일 레코드를 삽입하려면 다음 문을 사용할 수 있습니다.

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

이중 테이블을 사용하면 값이 현재 테이블에 저장되어 있지 않더라도 select 문에 값을 입력 할 수 있습니다.

Where 절을 사용하여 삽입하는 방법 도 참조하십시오.

---

이 질문에 대한 정답은 다음과 같습니다.

ㅏ). 삽입하기 전에 선택하려면 :

INSERT INTO Users( weight, desiredWeight ) 
  select val1 , val2  from tableXShoulatNotBeUsers
  WHERE somecondition;

비). 레코드가 이미있는 경우 삽입 대신 업데이트를 사용하십시오.

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

해야한다

Update Users set weight=160, desiredWeight=145  WHERE id = 1;

씨). 동시에 업데이트 또는 삽입하려는 경우

Replace Users set weight=160, desiredWeight=145  WHERE id = 1;

Note):- you should provide values to all fields else missed field in query 
        will be set to null

d). If you want to CLONE a record from SAME table, just remember you cann't select from table to which you are inserting therefore

 create temporary table xtable ( weight int(11), desiredWeight int(11) ;

 insert into xtable (weight, desiredWeight) 
    select weight, desiredWeight from Users where [condition]

 insert into Users (weight, desiredWeight) 
    select weight , desiredWeight from xtable;

I think this pretty covers most of the scenarios

---

You simply cannot use WHERE when doing an INSERT statement:

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

should be:

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 );

The WHERE part only works in SELECT statements:

SELECT from Users WHERE id = 1;

or in UPDATE statements:

UPDATE Users set (weight = 160, desiredWeight = 145) WHERE id = 1;

---

Insert into = Adding rows to a table

Upate = update specific rows.

What would the where clause describe in your insert? It doesn't have anything to match, the row doesn't exist (yet)...

---

You can do conditional INSERT based on user input. This query will do insert only if input vars '$userWeight' and '$userDesiredWeight' are not blank

INSERT INTO Users(weight, desiredWeight )
select '$userWeight', '$userDesiredWeight'  
FROM (select 1 a ) dummy
WHERE '$userWeight' != '' AND '$userDesiredWeight'!='';

---

It depends on the situation INSERT can actually have a where clause.

For example if you are matching values from a form.

Consider INSERT INTO Users(name,email,weight, desiredWeight) VALUES (fred,bb@yy.com,160,145) WHERE name != fred AND email != bb@yy.com

Makes sense doesn't it?

---

The simplest way is to use IF to violate your a key constraint. This only works for INSERT IGNORE but will allow you to use constraint in a INSERT.

INSERT INTO Test (id, name) VALUES (IF(1!=0,NULL,1),'Test');

---

After WHERE clause you put a condition, and it is used for either fetching data or for updating a row. When you are inserting data, it is assumed that the row does not exist.

So, the question is, is there any row whose id is 1? if so, use MySQL UPDATE, else use MySQL INSERT.

---

If you are specifying a particular record no for inserting data its better to use UPDATE statement instead of INSERT statement.

This type of query you have written in the question is like a dummy query.

Your Query is :-

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

Here , you are specifying the id=1 , so better you use UPDATE statement to update the existing record.It is not recommended to use WHERE clause in case of INSERT.You should use UPDATE .

Now Using Update Query :-

UPDATE Users SET weight=160,desiredWeight=145 WHERE id=1;

---

Does WHERE-clause can be actually used with INSERT-INTO-VALUES in any case?

The answer is definitively no.

Adding a WHERE clause after INSERT INTO ... VALUES ... is just invalid SQL, and will not parse.

The error returned by MySQL is:

mysql> INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1

The most important part of the error message is

... syntax to use near 'WHERE id = 1' ...

which shows the specific part the parser did not expect to find here: the WHERE clause.

---

its totall wrong. INSERT QUERY does not have a WHERE clause, Only UPDATE QUERY has it. If you want to add data Where id = 1 then your Query will be

UPDATE Users SET weight=160, desiredWeight= 145 WHERE id = 1;

---

No. As far as I am aware you cannot add the WHERE clause into this query. Maybe I've forgotten my SQL too, because I am not really sure why you need it anyway.

---

You Should not use where condition in Insert statement. If you want to do, use insert in a update statement and then update a existing record.

Actually can i know why you need a where clause in Insert statement??

Maybe based on the reason I might suggest you a better option.

---

I think your best option is use REPLACE instead INSERT

REPLACE INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

---

DO READ THIS AS WELL

It doesn't make sense... even literally

INSERT means add a new row and when you say WHERE you define which row are you talking about in the SQL.

So adding a new row is not possible with a condition on an existing row.

You have to choose from the following:

A. Use UPDATE instead of INSERT

B. Use INSERT and remove WHERE clause ( I am just saying it...) or if you are real bound to use INSERT and WHERE in a single statement it can be done only via INSERT..SELECT clause...

INSERT INTO Users( weight, desiredWeight ) 
SELECT FROM Users WHERE id = 1;

But this serves an entirely different purpose and if you have defined id as Primary Key this insert will be failure, otherwise a new row will be inserted with id = 1.

---

I am aware that this is a old post but I hope that this will still help somebody, with what I hope is a simple example:

background:

I had a many to many case: the same user is listed multiple times with multiple values and I wanted to Create a new record, hence UPDATE wouldn't make sense in my case and I needed to address a particular user just like I would do using a WHERE clause.

INSERT into MyTable(aUser,aCar)
value(User123,Mini)

By using this construct you actually target a specific user (user123,who has other records) so you don't really need a where clause, I reckon.

the output could be:

aUser   aCar
user123 mini
user123 HisOtherCarThatWasThereBefore

---

A way to use INSERT and WHERE is

INSERT INTO MYTABLE SELECT 953,'Hello',43 WHERE 0 in (SELECT count(*) FROM MYTABLE WHERE myID=953); In this case ist like an exist-test. There is no exception if you run it two or more times...

---

correct syntax for mysql insert into statement using post method is:

$sql="insert into ttable(username,password) values('$_POST[username]','$_POST[password]')";

---

i dont think that we can use where clause in insert statement

---

INSERT INTO Users(weight, desiredWeight )
SELECT '$userWeight', '$userDesiredWeight'  
FROM (select 1 a ) dummy
WHERE '$userWeight' != '' AND '$userDesiredWeight'!='';

---

You can't use INSERT and WHERE together. You can use UPDATE clause for add value to particular column in particular field like below code;

UPDATE Users
SET weight='160',desiredWeight ='145'  
WHERE id =1

---

I think that the correct form to insert a value on a specify row is:

UPDATE table SET column = value WHERE columnid = 1

it works, and is similar if you write on Microsoft SQL Server

INSERT INTO table(column) VALUES (130) WHERE id = 1;

on mysql you have to Update the table.

참고URL : https://stackoverflow.com/questions/485039/mysql-insert-where-query