Nico Posts - Answer Overflow

Nico

Explore posts from servers

•Created by Nico on 9/28/2024 in #help

Subqueries in insert statements

I have the following schema (in sqlite)

CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );

CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );

When inserting into job_titles, in SQL, I've done the following to get the correct language_id given a language.code:

-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );

-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );

Is there a way to do this in Drizzle outside of using the sql function and writing the wrong string? Right now, I've just writen a Typescript function to recreate the functionality but I just want to know if there's other approaches

3 replies

Gaming

Programming