CODEWITHSUNDEEP
mysqlselectsql-update
niks
niks

Reputation: 659

How to use update together with select...for update in mysql

I have two MySql tables tableA and tableB, primary key "id" in tableA is used as a foreign key "parent_id" in tableB. I would like to update single row in tableB using select...for update so that other users can not access it while transaction is not over. My question is - how to correctly update selected row in one query? Here is my sample code: 

START TRANSACTION;
SELECT b.reserved, b.owner FROM tableB b, tableA a 
WHERE b.parent_id = a.id AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180' FOR UPDATE;
UPDATE tableB SET...;
COMMIT;

Thank you!

Upvotes: 0

Views: 130

Answers (1)

Miroslav Glamuzina
Miroslav Glamuzina

Reputation: 4587

Yes, this is possible. Please take a look at:

Here is an example of what your query may look like: 

START TRANSACTION;
# Lock table using `FOR UPDATE`
SELECT 
  b.reserved, 
  b.owner 
FROM 
  tableB b,
  tableA a 
WHERE 
  b.parent_id = a.id 
  AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180' 
FOR UPDATE;

# Update query
UPDATE 
  tableA 
SET 
  tableA.column1=(
    SELECT 
      b.reserved 
    FROM 
      tableB b, 
      LEFT JOIN tableA a ON a.id=b.id 
    WHERE 
      b.parent_id = a.id 
      AND a.guid ='5344a990-fedf-4deb-a114-0d5d6a3ba180'
  ) 
WHERE ... 
LIMIT 1;  

COMMIT;

Hope this helps,

Upvotes: 1

Related Questions