Syntax error in mysql?

Question

I'm used to MS sql so working with mySql is sure to throw some syntax error at me that is bound to slow me down. I have the following:

declare @expensesSum DOUBLE
select @expensesSum = sum(expenseAmount) from bondsaverdb.expenses
insert into bondsaverdb.expenses
select '6','Extracash',(income - @expensesSum)  from bondsaverdb.income where userName ='Dean'

The error I'm getting says:

syntax error near declare @expensesSum

Must be the way I'm declaring the variable??

Thanks in advance!

This'll help :) http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — dweiss, May 02 '12 at 13:45
What comes before this? You might be missing a delimiter `;` which MS SQL doesn't need but MySQL does. Post the entire procedure this is a part of. — Michael Berkowski, May 02 '12 at 13:45
Hope u knew this "DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements". http://dev.mysql.com/doc/refman/5.0/en/declare.html — sree, May 02 '12 at 13:45

score 1 · Accepted Answer · answered May 02 '12 at 13:50

1

MySQL does not require (or even allow, apparently) you to declare variables, and as far as I know it will treat all objects, variables included, as strings -- so they cannot be typed in t his way. Anyway, this should work:

SET @expensesSum = (SELECT SUM(expenseAmount) FROM bondsaverdb.expenses);
INSERT INTO
   bondsaverdb.expenses
SELECT
   '6', 'Extracash', (income - @expensesSum)
FROM
   bondsaverdb.income
WHERE
   userName = 'Dean'

I'm also not sure what that '6' is for .. if that's an auto increment ID you should omit it from the INSERT altogether (and specify the other columns to write to) or leave it NULL.

answered May 02 '12 at 13:50

Explosion Pills

188,624
52
326
405

You can declare variables inside stored procedures. – Andreas Wederbrand May 02 '12 at 13:57
@AndreasWederbrand thanks, that could be useful if this is intended to be a stored procedure – Explosion Pills May 02 '12 at 14:17

score 1 · Answer 2 · answered May 02 '12 at 13:50

MySQL is very different to SQL Server; you don't declare variables generally

SET @expensesSum = (select @expensesSum = sum(expenseAmount) from bondsaverdb.expenses);
insert into bondsaverdb.expenses
select '6','Extracash',(income - @expensesSum)  from bondsaverdb.income where userName ='Dean';

Syntax error in mysql?

2 Answers2