Syntax error on UPDATE query

Question

I am trying to take the data from excel sheet cells and update the mysql table using this query

sqlstr = "UPDATE " & table4 & " SET price = CASE WHEN key_id = '" & Cells(i, 1) & "' " & " THEN " & Cells(i, 8) & " " & " WHEN key_id = '" & Cells(i, 2) & "' " & " THEN " & Cells(i, 9) & " " & " ELSE: price "

It gives me an error:

Syntax to use near ':price'

Well, if i remove at the end the word price it gives me an error:

Syntax to use near "

I guess I am missing some quotes somewhere. How can I fix this issue?

Please show the results of sqlstr after it has been created. — Gordon Linoff
– Gordon Linoff, Commented Mar 29, 2014 at 16:03
I don't understand what you mean. The query doesn't even go through — user3465207
– user3465207, Commented Mar 29, 2014 at 16:08
Print the value of sqlstr so we can see what it looks like after variable substitution. — Gordon Linoff
– Gordon Linoff, Commented Mar 29, 2014 at 16:09

Ravinder Reddy · Accepted Answer · 2014-03-29 16:02:38Z

1

Error 1:

" ELSE: price shoule be " ELSE " & price

Error 2:

You are missing and END for the CASE clause

sqlstr = "UPDATE " & table4 & " SET price = CASE WHEN key_id = '" & Cells(i, 1) & "' THEN " & Cells(i, 8) & " WHEN key_id = '" & Cells(i, 2) & "' THEN " & Cells(i, 9) & " ELSE " & price & " END"

edited Mar 29, 2014 at 16:02

answered Mar 29, 2014 at 15:57

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3465207 Over a year ago

It says Syntax near ':price END" at line 1

user3465207 Over a year ago

Oh holy crap!!!! IT WORKS!!!!!!!!!!!!!! THANKK YOUUUU SO MUCH!!!!!! You the man!!!

user3465207 Over a year ago

How can I start to update the table from the excel sheet from row two and not the row one. Because row one contains the name fields

Wayne G. Dunn Over a year ago

It appears you are using a loop with 'i' being the ROW. If you had 'For i = 1 to 999', then change it to 'For i = 2 to 999' the solution is setting the value of 'i'

Ravinder Reddy Over a year ago

The rows loop should be 2nd to Rows.count

|

Collectives™ on Stack Overflow

Syntax error on UPDATE query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related