CODEWITHSUNDEEP
sqlmariadb
s0Nic
s0Nic

Reputation: 97

Insert result into temp table mariadb

i am trying to insert my results to a temporary table i created before. Dont know why this is not working. Need help thank you. Code:

SET @serial = '75900101';
DROP TEMPORARY TABLE IF EXISTS machinenametable;
CREATE TEMPORARY TABLE machinenametable (id INT, name VARCHAR(100));

WITH machine_data AS (
SELECT
    *
FROM
    machine m
WHERE 
    @serial = m.serial
), machine_id AS (
SELECT
    1 AS id,
    (IF(
        md.identifier LIKE 'PP%',
        (SELECT 'PP'),
        IF(
            md.identifier LIKE 'SHL',
            (SELECT 'SHL'),
            (SELECT 'SL')
        )
    ))  AS machine
FROM machine_data md
)
INSERT INTO machinenametable (id, name)
SELECT id, name FROM machine_id

Something with this Insert into is wrong but i dont have any idea why.... Getting a Error Msg :

SQL-Error[1064] [42000]: (conn=3127818) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO machinenametable (id, name)

EDIT: This is Working:

INSERT INTO machinenametable (id, name)
    SELECT
        1 AS id,
            (CASE WHEN m.identifier LIKE 'PP%' THEN 'PP'
             WHEN m.identifier LIKE 'SHL' THEN 'SHL'
             ELSE 'SL'
             END) AS name
    FROM machine m
    WHERE @serial = m.serial

Upvotes: 1

Views: 1707

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You have very complicated logic for something that seems rather simple. I think this does what you want:

INSERT INTO machinenametable (id, name)
    SELECT 1,
           (CASE WHEN md.identifier LIKE 'PP%' THEN 'PP'
                 WHEN md.identifier LIKE 'SHL' THEN 'SHL'
                 ELSE 'SL'
            END)
    FROM machine m
    WHERE  @serial = m.serial;

The CTE do nothing useful. And the use of IF instead of CASE is convoluted. And using SELECT for constants is simply unnecessary.

Upvotes: 1

mudgal
mudgal

Reputation: 21

I think the issue is with the usage of the insert command.

Edit the INSERT command and use as

INSERT INTO machinenametable VALUES(id, name)

Upvotes: 1

Related Questions