CODEWITHSUNDEEP
sqlsqlite
DeKinci
DeKinci

Reputation: 53

Get value from table to use it in insert

I suppose this question was already asked somewhere here, but I have no idea how to name (and look for) it correctly.

The database:

CREATE TABLE department (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employee (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    department_id INTEGER      NOT NULL,
    chief_id      INTEGER,
    name          VARCHAR(100) NOT NULL UNIQUE,
    salary        INTEGER      NOT NULL,

    FOREIGN KEY (department_id) REFERENCES department (id),
    FOREIGN KEY (chief_id) REFERENCES employee (id)
);

INSERT INTO department (name) VALUES ('sales'), ('it'), ('management');

INSERT INTO employee (department_id, chief_id, name, salary) VALUES
    (3, NULL, 'Owner', 1000000),
    (2, 1, 'Team manager', 9000),
    (2, 3, 'Senior dev #1', 7000),
    (2, 3, 'Senior dev #2', 7000);

Now in insert I should calculate chief_id on my own, but I'm curious if there is a possibility to get id by name, something like

SELECT id FROM employee WHERE name = 'Owner'

and use this value instead of hardcoded id in insert.

I've tried putting select statement instead of id but that does not work.

I am using SQLite.

Upvotes: 2

Views: 29

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You can do it with a subquery as yours, but the data has to be in the table before you can select it. Otherwise the subquery returns null. So you need to break the one INSERT into multiple INSERTs.

INSERT INTO employee (department_id, chief_id, name, salary) VALUES
    (3, NULL, 'Owner', 1000000);

INSERT INTO employee (department_id, chief_id, name, salary) VALUES    
    (2, (SELECT id FROM employee WHERE name = 'Owner'), 'Team manager', 9000);

INSERT INTO employee (department_id, chief_id, name, salary) VALUES
    (2, (SELECT id FROM employee WHERE name = 'Team manager'), 'Senior dev #1', 7000),
    (2, (SELECT id FROM employee WHERE name = 'Team manager'), 'Senior dev #2', 7000);

But note, that the subquery must return only one row. So the column you're checking in it's WHERE clause must contain unique values. Usually a name is not unique, there are a lot of John Smith. Usually the ID is the unique identifier. So in a general inserting the ID directly is the right approach.

Upvotes: 3

Related Questions