[SOLVED] Can you insert into a table where a value is a declared variable in mysql?

Can you insert into a table where a value is a declared variable in mysql?

Is it possible to do something like this ?

INSERT INTO homework(teacherId, class, name, dueDate)
VALUES(teacherId, "12A1", "trig", "tomorrow")
teacherID = (
        SELECT teacherId
        FROM teacherlogins
        WHERE teacherid = 5
    )

I know this query is incorrect, but is it possible to do this another way?

Solution

There are several ways to do it. One is in the comment above from Barbara, using INSERT...SELECT syntax:

INSERT INTO homework(teacherId, class, name, dueDate)
SELECT teacherId, '12A1', 'trig', 'tomorrow'
FROM teacherlogins
WHERE teacherid = 5;

Here's another using a scalar subquery operand:

INSERT INTO homework(teacherId, class, name, dueDate)
VALUES((SELECT teacherId FROM teacherlogins WHERE teacherid = 5 LIMIT 1),
    '12A1', 'trig', 'tomorrow')

Here's another using a user-defined variable and SELECT INTO syntax:

SELECT teacherId INTO @t FROM teacherlogins WHERE teacherid = 5;

INSERT INTO homework(teacherId, class, name, dueDate)
VALUES(@t, '12A1', 'trig', 'tomorrow');

Here's another: :-)

INSERT INTO homework(teacherId, class, name, dueDate)
VALUES(5, '12A1', 'trig', 'tomorrow');