title: How to update value in row while also using that row in a sub-queries?
body: <p>I am trying to make a room booking system where once somebody requests a booking they are sent a pin number and the booking isn't confirmed until they send that pin number back.</p>
  
  <p>I have made it up to the point where the user can request a booking and it generates a pin number and adds this to the correct table, however I am stuck when trying to update the status of the booking if the pin and username that they enter match a pin number and booking combination.</p>
  
  <p>The user will enter their username and pin number and I want to:</p>
  
  <ol>
  <li>update b_status (of the booking where p_bookingId matches b_id) to 'confirmed'</li>
  <li>delete the row in the pin numbers table that had their pin number in.</li>
  </ol>
  
  <p>In the example of my tables below:</p>
  
  <ol>
  <li>Somebody would enter 'jeremy' and '546365'</li>
  <li>b_status of the row with b_id=2 would be updated to 'confirmed'</li>
  <li>The row in the pins table would get deleted</li>
  </ol>
  
  <p>I have 3 tables: 'users', 'bookings', and 'pins'. </p>
  
  <pre><code>users
  ------------------
  u_id | u_username
  ------------------
  1    | jane
  2    | johnny
  3    | jeremy
  </code></pre>
  
  <p>.</p>
  
  <pre><code>bookings
  ---------------------------------
  b_id | b_userId | b_status
  ---------------------------------
  1    | 2        | pending confirm
  2    | 3        | pending confirm
  </code></pre>
  
  <p>.</p>
  
  <pre><code>pins
  --------------------------------
  p_id | p_bookingId | p_pinNumber
  --------------------------------
  1    | 2           | 546365
  </code></pre>