Welcome to WebmasterWorld Guest from 54.166.133.84

Forum Moderators: open

MySQL: set a variable from an INSERT SELECT

     
7:52 am on Feb 19, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1022
votes: 96


I currently use a set of statements that look like this:

SET @var_id = 12345;
SET @var_date = 20190219081939;

INSERT INTO tableA
SELECT NULL, id, colB, colC, colD FROM tableB
WHERE id=@var_id AND postdate=@var_date LIMIT 1;

UPDATE tableC SET
colE = (SELECT colB FROM tableA WHERE id=@var_id LIMIT 1),
colF = (SELECT colC FROM tableA WHERE id=@var_id LIMIT 1),
colG = (SELECT COUNT(1) FROM tableA WHERE id=@var_id)
WHERE id=@var_id;


I originally set it up to use both @var_id and @var_date, just in case there was ever a time where two rows had the same @var_date. But in 16 years that's never actually happened, so I'm trying to simplify it on my end by eliminating the SET @var_id statement altogether.

The problem, though, is that I still need @var_id in the UPDATE statement.

I'm pretty sure that I could do this:

SET @var_date = 20190219081939;
SELECT id INTO @var_id FROM tableB WHERE postdate=@var_date LIMIT 1;


But I'm curious, since I'm already querying tableB in the INSERT..SELECT statement, is there a way to just set @var_id from there? It would cut down on the statements, but this is mainly just for my own education.

I know this won't work, but it should give you an idea of what I'm trying to do:

SET @var_date = 20190219081939;

INSERT INTO tableA
SELECT NULL, id, colB, colC, colD FROM tableB
WHERE postdate=@var_date LIMIT 1
INTO @var_junkA, @var_id, @var_junkB, @var_junkC, @var_junkD;