Databases
StackOverflow2013
Postsdbo
POselect 2 tables copy a unique number when records match
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POselect 2 tables copy a unique number when records match
    text
    copied!<p>I have 2 tables 1. paypal_ipn_orders table 2 is lookup. The lookup table I have a list of all of my items with the column name item I also have another column labeled sort_id which is an 8 digit number used internally for all of my items. Table 1 paypal_ipn_orders receives orders and has several columns but only a few are important to the question. id, payer_email, item_name, sort_num and payment_status. What I need to happen is when an order comes into table paypal_ipn_orders I need to cross reference the lookup table and assign the 8 digit number into the column labeled sort_num when the item names match. </p> <pre><code>table: paypal_ipn_orders id payer_email item_name sort_num payment_status 1 greg@gmail.com batman card NULL completed 2 steve@gmail.com superman card NULL completed 3 gary@gmail.com spiderman card NULL completed 4 sarah@gmail.com captain america NULL completed </code></pre> <p>here is the sample look up table</p> <pre><code>table: lookup item sort_id batman card 12345678 superman card 12345677 spiderman card 12345676 captain america 12345675 </code></pre> <p>what I want the output to look like after I run the query.</p> <pre><code>table: paypal_ipn_orders id payer_email item_name sort_num payment_status 1 greg@gmail.com batman card 12345678 completed 2 steve@gmail.com superman card 12345677 completed 3 gary@gmail.com spiderman card 12345676 completed 4 sarah@gmail.com captain america 12345675 completed </code></pre> <p>I know this isnt exactly the right syntax but you'll get the idea</p> <pre><code>SELECT lookup.sort_id INSERT INTO paypal_ipn_orders.sort_num WHERE lookup.item = paypal_ipn_orders.item_name </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload