0
INSERT INTO `table`(
    `name`,
    `user`
)
VALUES(
    'this is name',
    'user id should be here'
);

The problem is that where I have to enter the user id, I must recognize it from another table, BUT if it is not there, then create a user, get its id and insert it into the field. It would be possible to get an ID and paste

INSERT INTO `table`(
    `name`,
    `user`
)
VALUES(
    'this is name',
    (SELECT `id` FROM `users` WHERE `name` = 'test user')
);

But the task is such that if there is no such user, then create it and get an ID... I don't know how do that, help pls MySQL 5.7

INSERT INTO `table`(
    `name`,
    `user`
)
VALUES(
    'this is name',
    (IF((SELECT `id` FROM `positions` WHERE `name` = 'ТЕСТОВАЯ ДОЛЖНОСТЬ'), (SELECT `id` FROM `positions` WHERE `name` = 'ТЕСТОВАЯ ДОЛЖНОСТЬ'), (INSERT INTO `positions` (`name`) VALUES ('ТЕСТОВАЯ ДОЛЖНОСТЬ')))
);

It's not working...

Stepback
  • 1
  • 1
  • MySQL 5.1 are you sure? that's ancient.. – P.Salmon Mar 28 '22 at 12:17
  • @P.Salmon sorry, 5.7, legacy project,but the problem is relevant I need to create an entry in the table "table1", it has a "user" field, it takes an int value of the user id from another table. Accordingly, when I make an entry in the "table1" table, should I check whether there is such a user? if there is, just return its id and as a result a record will be created in the "table1" table with the specified name and the id value will be in the "user" field, and if there is no such user, then create this user, get its id and insert it into the record creation "table1" this id in the "user" field – Stepback Mar 28 '22 at 15:37
  • You can only insert to one table at a time. I suggest you create a procedure testing if user exists (which is dubious given that all you have to go in is name) insert if it doesn't, capture last_insert_id and insert to your table. eg https://stackoverflow.com/questions/3837990/last-insert-id-mysql – P.Salmon Mar 29 '22 at 06:59

1 Answers1

0

Your second version can be rewritten as an INSERT INTO ... SELECT:

INSERT INTO yourTable (name, user)
SELECT 'this is name', id
FROM users
WHERE name = 'test user';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • This does not work. I need to create an entry in the table "table1", it has a "user" field, it takes an int value of the user id from another table. Accordingly, when I make an entry in the "table1" table, should I check whether there is such a user? if there is, just return its id and as a result a record will be created in the "table1" table with the specified name and the id value will be in the "user" field, and if there is no such user, then create this user, get its id and insert it into the record creation " table1" this id in the "user" field – Stepback Mar 28 '22 at 11:06