MySQL Declaration PHPMyADMIN

Question

Im want to run queries with declaraions in PHPMYADMIN.

Here my querycode

declare @shopdomain varchar(30);
SET @shopdomain = 'newdomain.com';
UPDATE trans SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE settings SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE...

PHPMYADMIN shows this error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare @shopdomain varchar(30)' at line 1

What im doing wrong?

I believe you need DECLARE shopadmin varchar(30) DEFAULT 'newdomain.com' (without the @) — Rottingham, Jan 08 '14 at 19:40

score 8 · Accepted Answer · answered Jan 08 '14 at 19:45

DECLARE available only in a context of a stored routine (procedure, function, trigger, event)
You're mixing local variables (without @ in front of their names) with user(session) variables

That being said if you want to go with local variables you do something like this

DELIMITER $$
CREATE PROCEDURE my_proc()
BEGIN
  DECLARE shopdomain VARCHAR(30);
  SET shopdomain = 'newdomain.com';
  UPDATE trans SET tval = REPLACE(name,'olddomain.de', shopdomain ) WHERE name LIKE 'olddomain.de';
  UPDATE settings SET tval=REPLACE(name,'olddomain.de', shopdomain ) WHERE name LIKE 'olddomain.de';
  UPDATE ...
END$$
DELIMITER ;

And then call your procedure

CALL my_proc();

If you go with session variables then you can execute it right away without creating a procedure in the following manner

SET @shopdomain = 'newdomain.com';
UPDATE trans SET tval = REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE settings SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE ...

MySQL Declaration PHPMyADMIN

1 Answers1