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!

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
– Michael Berkowski, Commented May 2, 2012 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". dev.mysql.com/doc/refman/5.0/en/declare.html — sree
– sree, Commented May 2, 2012 at 13:45

Explosion Pills · Accepted Answer · 2012-05-02 13:50:21Z

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 2, 2012 at 13:50

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

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

2 Comments

Andreas Wederbrand Over a year ago

You can declare variables inside stored procedures.

Explosion Pills Over a year ago

@AndreasWederbrand thanks, that could be useful if this is intended to be a stored procedure

gbn · Accepted Answer · 2012-05-02 13:50:56Z

1

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';

answered May 2, 2012 at 13:50

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

Syntax error in mysql?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related