MySql create variable from sql select

Question

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

Gordon Linoff · Accepted Answer · 2018-11-19 12:23:49Z

2

Are you trying to do this?

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

answered Nov 19, 2018 at 12:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Raymond Nijland Over a year ago

@gstackoverflow 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

gstackoverflow Over a year ago

@Raymond Nijland, thanks for reminder but as you can see I provided that I use column with name UNIQ_COLUMN

Raymond Nijland Over a year ago

@gstackoverflow Ok how should i know maybe UNIQ meant something unix quality or unix quantity or something else like that

Raymond Nijland · Accepted Answer · 2018-11-19 12:31:53Z

1

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

answered Nov 19, 2018 at 12:31

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySql create variable from sql select

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related