Creating, setting then using a variable within the same mysql statement

Question

I'm trying to work with variables inside a mysql statement and struggling.

I've got this at the moment...

 SET @customer = 'UNKNOWN';
 SET @customer = (SELECT account FROM contacts WHERE emailaddy = 'myemailaddress' LIMIT 1);

 INSERT INTO cases
 (customer, details,severity,currentowner,created,createdautomatically)
 VALUES (@customer, 'This is a test case','SYS',now(),TRUE)

I'm getting 'Error. Parameter '@customer' must be defined'.

Can anyone point me in the right direction?

EDIT : After being told I was missing the declare, I've added this and I'm still getting the same error...

 DECLARE @customer VARCHAR(200);
 SET @customer = 'UNKNOWN';
 SET @customer = (SELECT account FROM contacts WHERE emailaddr = 'myemailaddress' LIMIT 1);

 INSERT INTO cases(customer, ....) VALUES (@customer, .....)

 Error. Parameter '@customer' must be defined..

ANOTHER EDIT....

Still doesnt seem to be working correctly :(

 DECLARE @customer VARCHAR(200);
 SET @customer := 'UNKNOWN';
 SET @customer := (SELECT account FROM contacts WHERE email = 'me');
 INSERT INTO cases (customer, ...) VALUES (@customer, '...')

 Error. Parameter '@customer' must be defined..

You need to have a DECLARE @customer <datatype> somewhere before you SET it. — Siyual
– Siyual, Commented Jun 29, 2016 at 17:47
More over in the INSERT INTO you have 6 columns defined but passing only 5 values for the same. Hope severity's equivalent value is not passed. — Arulkumar
– Arulkumar, Commented Jun 29, 2016 at 17:50
Thanks Arulkumar... I've stripped alot of the real info out before posting to make sure I don't accidentally breach data protection :) — John
– John, Commented Jun 29, 2016 at 17:54

Mahesh Madushanka · Accepted Answer · 2016-06-29 18:11:57Z

1

the issue is with the 1st line

mysql> select "sss" into @customer;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO funding(dONATUR) VALUES (@customer);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM funding WHERE Donatur='sss';
+------+--------+---------+
| area | client | Donatur |
+------+--------+---------+
| NULL | NULL   | sss     |
+------+--------+---------+
1 row in set (0.00 sec)

edited Jun 29, 2016 at 18:11

answered Jun 29, 2016 at 17:55

Mahesh Madushanka

3,0032 gold badges17 silver badges29 bronze badges

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

6 Comments

John Over a year ago

So what I'm trying to say there is 'if a result is found in contacts, make that the the value of @customer, else make the value 'UNKNOWN'.... does that make sense?

Mahesh Madushanka Over a year ago

your issue is missing ":" value before = mark i have added it for you SET @customer := (SELECT account FROM contacts WHERE emailaddr = 'myemailaddress' LIMIT 1);

John Over a year ago

See my latest edit Mahesh; It's not playing ball at all :(

Mahesh Madushanka Over a year ago

sorry for the inconvenient due to previous answer

John Over a year ago

no worries Mahesh - thanks for taking the time to help! I'm struggling to use your example to successfully modify my code though. should it be like this... SELECT (SELECT name FROM contacts WHERE email = 'myaddress' LIMIT 1) into @customer;

|

Arulkumar · Accepted Answer · 2016-06-29 18:13:02Z

0

Could you please try with this query:

SELECT @customer := COALESCE(`account`, 'UNKNOWN') FROM `contacts` WHERE `emailaddr` = 'myemailaddress' LIMIT 1;

INSERT INTO `cases` (customer, ...) VALUES (@customer, '...');

answered Jun 29, 2016 at 18:13

Arulkumar

13.3k14 gold badges51 silver badges76 bronze badges

Collectives™ on Stack Overflow

Creating, setting then using a variable within the same mysql statement

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related