scripting with variables

[SimonSellick]

Hi,

I am trying to develop a data creation script using variables whose values aren't known until the script runs. In SQL Server the syntax would be something like this:

declare @a integer;
select @a = id from ref where code = 'A';
insert into tbl(col, ref) values ('fred', @a);
go

I know that I can write the scripts to use

INSERT ... SELECT

...but it's going to look a bit clumsy and be much longer and harder to maintain. Does anyone know if there is a MySQL equivalent to the code above that works outside a procedure? The following fails with a syntax error:

delimiter /go
declare aa integer;
select aa = id from ref where code = 'AA';
insert into tbl(col, ref) values ('fred', aa);
/go

Sorry if this is a bit basic, but I cannot find anything in the 5.1 manual. Should I have bought a book?

Any help / pointers would be appreciated.

 

[r937]

...but it's going to look a bit clumsy and be much longer and harder to maintain

no it's not



INSERT
INTO tbl
( col, ref )
SELECT 'fred', id
FROM ref
WHERE code = 'A'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SimonSellick]

OK, fair enough. I'd more-or-less reached the same conclusion. Thanks for your response

Simon

 

[DonQuichote]

SELECT id INTO @a FROM ref WHERE code = 'A';
INSERT INTO tbl(col, ref) VALUES('fred', @a);

Note that unlike in SQL server, the variables live as long as your connection. The connection in MySQL is more of a session.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[SimonSellick]

Aha! I thought that I had tried that and got a syntax error - but I actually had tried

SELECT @a = id ...

instead of

SELECT id INTO @a ...

Obviously, too much SQL Server. Thank you!