CODEWITHSUNDEEP
mysqlsqlvariables
gstackoverflow
gstackoverflow

Reputation: 37034

MySql create variable from sql select

Is there way to create variable from select ?

set @MY_ID = 'SELECT ID FROM TMS.MY_TABLE WHERE MY_TABLE.UNIQ_COLUMN= 2134';

Code above works but it just saves string:

SELECT @MY_ID; returns

SELECT ID FROM TMS.MY_TABLE WHERE MY_TABLE.UNIQ_COLUMN= 2134'

I have to know this id to use it as foreign key to insert into another tables

Upvotes: 1

Views: 60

Answers (2)

Raymond Nijland
Raymond Nijland

Reputation: 11602

The other variation would be 

SELECT 
 ID
FROM 
 TMS.MY_TABLE
INTO @MY_ID
WHERE
 MY_TABLE.UNIQ_COLUMN = 2134

Be sure the result can only have one record. So most likely you need to add ORDER BY column LIMIT 1 if MY_TABLE.UNIQ_COLUMN not has a unique or primary key

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Are you trying to do this?

SELECT @MY_ID := ID
FROM TMS.MY_TABLE
WHERE MY_TABLE.UNIQ_COLUMN = 2134;

Upvotes: 2

Related Questions