I am trying to replicate this raiserle sample code
But I am having this error:
{
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
sqlMessage: "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 'LOCK TABLES `individual` WRITE INSERT INTO individual (email, last_name, firs...' at line 1",
sql: "BEGIN LOCK TABLES `individual` WRITE INSERT INTO individual (email, last_name, first_name, phone_number, auth_string, suppress_email_sending, is_participant, org_id, suborgs, roles, initiation_date, created_at, modified_at, created_by, modified_by) VALUES ('king23garoo@sample.com', 'Garoo', 'King', NULL, '111111111100000000000000000111111111111111111110000001000', 0, 1, 1, '1', '6', '2022-06-01T10:39', NOW(), NOW(), '1', '1')"
}
This is my code:
db.query(
"BEGIN LOCK TABLES `individual` WRITE INSERT INTO individual
(email, last_name, first_name, phone_number, auth_string,
suppress_email_sending, is_participant, org_id, suborgs,
roles, initiation_date, created_at, modified_at, created_by, modified_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, '?', '?', ?, NOW(), NOW(), ?, ?)",
[
data.email,
data.last_name,
data.first_name,
data.phone_number,
data.auth_string,
data.suppress_email_sending,
data.is_participant,
data.org_id,
data.suborgs,
data.roles,
data.initiation_date,
data.created_by,
data.modified_by,
],
(err) => {
console.log(err)
if (err) return result(err, null)
db.query("SET @last_ind_id = LAST_INSERT_ID()");
"UNLOCK TABLES"
db.query(
"INSERT INTO ind_group
(ind_id, program_id, iteration_id, org_id, suborg_id,
created_at, modified_at, created_by, modified_by)
VALUES (@last_ind_id, ?, ?, ?, ?, NOW(), NOW(), ?, ?) END",
[
data.indGroup.program_id,
data.indGroup.iteration_id,
data.org_id,
data.suborgs,
data.created_by,
data.modified_by,
],
(err, results) => {
console.log(results)
console.log(err)
if (err) return result(err, null)
result(null, results)
});
})
I have a unique constraint on the email, What I am trying to achieve is to put the LAST_INSERT_ID() from the individual table, into the ind_group table. Is this a good approach? or is there another way to get the last inserted id from table1 to be inserted in table2