execute insert and select statements consecutively after a case statement

Question

I want to execute insert and select statements once a condition is met on mysql, not sure how to do it.

##declare variables
SET @fvar = 'sometxt', @svar = 'sometxtagain';
SELECT @start := 1, @finish := 10;
select count(somecolumnID) as columnname,
case
  when count(somecolumnID) = '0' then 
  (
  ##insert statement here which will automatically generate a unique id, use @fvar here
  ##select statement here to get the unique id (@tvar) from first insert statement
  ##insert statement here which will automatically generate a unique 2nd_id (@fovar), use @svar and @tvar here
  ##select statement here to get the unique 2nd_id from second insert statement, use @fovar here
  )
  else 'record exist. nothing to do here'
end as output
from table_name where id = 'user_input';

score 0 · Answer 1 · answered Jan 26 '23 at 02:08

Do not use SELECT @var := expression; that has been deprecated (probably in a version after the one you are using). Instead use SET @var = constant_expression if practical, else use SELECT ... INTO @var.

Meanwhile, try to turn your query into a transaction:

BEGIN;
INSERT ...
SET @fvar = LAST_INSERT_ID(); -- if using auto_increment
SELECT ... INTO @fvar...;     -- if it is more complex
ditto for other INSERT+SELECT
SELECT ... CASE ... ...
COMMIT;

See if TRIGGERs would help. Or INSERT ... SELECT ...;. Pr INSERT ... ON DUPLICATE KEY UPDATE ....

execute insert and select statements consecutively after a case statement

1 Answers1