MySQL direct INSERT INTO with WHERE clause

Question

I tried googling for this issue but only find how to do it using two tables, as follows,

INSERT INTO tbl_member
SELECT Field1,Field2,Field3,... 
FROM temp_table
WHERE NOT EXISTS(SELECT * 
         FROM tbl_member 
         WHERE (temp_table.Field1=tbl_member.Field1 and
               temp_table.Field2=tbl_member.Field2...etc.)
        )

This worked for one scenario,But now my interest is to upload data directly from the program itself without using two tables. What i want is to upload the data which is not in the table. The sql i had in my head was like the following,

INSERT INTO tbl_member (SensorIdValue, DataTimeValue, DataInValue, IncompleteValue, SpiValue, InfoValue)
VALUES ('Sensor.org', '20121017150103', 'eth0','','','')
WHERE (SensorIdValue != 'Sensor.org'AND DataTimeValue != '20121017150103'AND DataInValue != 'eth0'AND IncompleteValue != ''AND SpiValue != ''AND InfoValue != '');

But it's wrong.. may i know the proper way of doing it please, Thank you very much :)

Based on the SQL you had in mind, it looks like you want to insert a row into a table if it doesn't already exist. If this is the case, please take a look at this question/answer. http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — quentinxs, Oct 18 '12 at 06:48
view this question. it might help. http://stackoverflow.com/questions/2930378/mysql-replace-into-alternative — Dev, Oct 18 '12 at 06:58
yeaaa thats exaclty was my need il check on these..thank you very much for the replies :) — Hasitha Shan, Oct 18 '12 at 07:06

score 21 · Accepted Answer · answered Oct 18 '12 at 06:40

21

INSERT syntax cannot have WHERE clause. The only time you will find INSERT has WHERE clause is when you are using INSERT INTO...SELECT statement.

The first syntax is already correct.

answered Oct 18 '12 at 06:40

John Woo

238,432
61
456
464

okey thank you, ill try optimizing the first query for my need :) – Hasitha Shan Oct 18 '12 at 06:42
yea how stupid of me just needed to call the same table in the select query and match it with the values i wanted.. :) thanks alot :) – Hasitha Shan Oct 18 '12 at 06:48

score 5 · Answer 2 · edited Oct 29 '18 at 06:13

5

you can use UPDATE command.

UPDATE table_name SET name=@name, email=@email, phone=@phone WHERE client_id=@client_id

edited Oct 29 '18 at 06:13

slfan

8,209
115
61
73

answered Oct 29 '18 at 05:52

Rj Raawat

51
1
1

score 1 · Answer 3 · edited Jul 19 '17 at 22:38

1

Example of how to perform a INSERT INTO SELECT with a WHERE clause.

INSERT INTO #test2 (id) SELECT id FROM #test1 WHERE id > 2

edited Jul 19 '17 at 22:38

Shashank

25
1
9

answered Oct 30 '15 at 08:31

Pomster

12,548
52
122
196

It's self explained. This is an example to show that you can INSERT INTO with a WHERE clause. it's a really simple example where anyone should be able to grasp the concept. – Pomster Nov 02 '15 at 07:20
Down vote is not mine, I like to comment instead. As you see, others disagree. I found your post in low quality posts, so I was suggesting you improve it. – Rohit Gupta Nov 02 '15 at 14:09
1

Cool, well i am going to leave it up even with the down vote as i do believe it could help someone later on. – Pomster Nov 02 '15 at 14:11

score 1 · Answer 4 · edited Jul 31 '16 at 13:22

If I understand the goal is to insert a new record to a table but if the data is already on the table: skip it! Here is my answer:

INSERT INTO tbl_member 
(Field1,Field2,Field3,...) 
SELECT a.Field1,a.Field2,a.Field3,... 
FROM (SELECT Field1 = [NewValueField1], Field2 = [NewValueField2], Field3 = [NewValueField3], ...) AS a 
LEFT JOIN tbl_member AS b 
ON a.Field1 = b.Field1 
WHERE b.Field1 IS NULL

The record to be inserted is in the new value fields.

score 1 · Answer 5 · edited Dec 14 '20 at 09:11

1

INSERT syntax cannot have WHERE but you can use UPDATE.

The syntax is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

edited Dec 14 '20 at 09:11

Adrian Mole

30,672
69
32
52

answered Dec 14 '20 at 03:32

Madhuraank B

43
6

score -3 · Answer 6 · edited Nov 02 '15 at 14:10

-3

The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

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

edited Nov 02 '15 at 14:10

Rohit Gupta

2,411
11
21
36

answered Oct 18 '12 at 06:48

hoang nguyen

1,727
4
19
20

thank you for the reply but i wanted to perform a conditional 'INSERT INTO' statement :) – Hasitha Shan Oct 18 '12 at 06:51

MySQL direct INSERT INTO with WHERE clause

6 Answers6

Linked