MySQL - Syntax error when using <> in a where statement (insert into)

Question

i was trying to build an 'insert if' mechanism in mysql with some conditions.

The topic that i followed to build it : MySQL INSERT IF (custom if statements)

The entire code is that :

insert into qualityScore (adGroupID, keywordID, qualityScore, day, clientID) 
select 222, 212, 6, '2016-07-23', 5
where ( select qualityScore from qualityScore where adGroupID=222 and keywordID = 212 and day < '2016-07-23' ) <> 5

But there is a little trouble with the below :

(select qualityScore from qualityScore where adGroupID=222 and keywordID = 212 and day < '2016-07-23')

When i execute this piece alone, it returns something like that :

       QualityScore
       ------------
       5

It's an expected result to get. But then the problem exists when i add to code the ' <> 5 ' part :

(select qualityScore from qualityScore where adGroupID=222 and keywordID = 212 and day < '2016-07-23') <> 5

It returns a MySQL 1064 syntax error code. As far as i know, syntax seems right and i can't find the point that leads to this problem.

Any suggestions will be appreciated. Thanks.

a) you are missing a `from` after `select 222, 212, 6, '2016-07-23', 5` and before the`where`, that is your error 1064. b) you will get further errors when/if your inner select returns more than 1 row (It's unclear what you want to achieve with this inner query, if you are sure it's always just one row, it will be fine) — Solarflare, Jul 24 '16 at 13:20
@Solarflare hey thanks for explanation. the entire query is working whether there is a 'from' or not. Actually does not working but not giving an error. But as i said, the problem is the inner query. it returns 5 but does not compare this result with the ' <> 5 ' part. — Cem, Jul 24 '16 at 13:27
For future reference, always mention the entire error *message*. The part about "the right syntax to use near ... *something-in-quotes*" may not seem helpful to you but it actually provides valuable information about where the parser encountered something contextually invalid, usually implying that the error was right before, if not at, the quoted string in the error, or the message will end with `near ''` (empty string, which means the entire query was parsed but is incomplete because there are no more tokens, which the parser believes to be wrong). — Michael - sqlbot, Jul 25 '16 at 03:34

Barmar · Accepted Answer · 2016-07-24T14:25:21.377

0

You can't have a WHERE clause without a FROM clause. If you're just selecting literals without a table, use the dummy table DUAL.

insert into qualityScore (adGroupID, keywordID, qualityScore, day, clientID) 
select 222, 212, 6, '2016-07-23', 5
from DUAL
where ( select qualityScore from qualityScore where adGroupID=222 and keywordID = 212 and day < '2016-07-23' ) <> 5

DEMO

edited Jul 24 '16 at 14:25

answered Jul 24 '16 at 13:42

Barmar

741,623
53
500
612

i guess i've done something wrong in the table names before. Now everything works fine, thank you very much for the demo and i really appreciate the help. – Cem Jul 24 '16 at 14:57

score -1 · Answer 2 · answered Jul 24 '16 at 13:14

-1

Try day < #2016-07-23#

Dates need # at the start and end.

answered Jul 24 '16 at 13:14

Dave B

659
8
29

hey there dave. it works fine with '' syntax right now. i've tried it with different date inputs. – Cem Jul 24 '16 at 13:28
This must be talking about a different DBMS. MySQL doesn't use `#` for this. – Barmar Jul 24 '16 at 13:40

MySQL - Syntax error when using <> in a where statement (insert into)

2 Answers2