Databases
StackOverflow2013
Postsdbo
POmysql select similar values when records match in 2 tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql select similar values when records match in 2 tables
    primarykey
    data
    text
    <p>I have 2 tables an <code>item_key</code> table and a <code>paypal_ipn_orders</code> table. In item_key I store <em>item names</em> and a *sort_id* which is an 8 digit number I use to sort the items, in the paypal_ipn_orders table I have an <em>item name</em> as well as a *sort_id*.</p> <p>What happens</p> <ol> <li>an order comes into> paypal_ipn_orders with an ITEM name</li> <li>a query executes on the item_key table checking for a matching ITEM name in the table</li> <li>If a match exists it assigns its 8 digit value(item_key.SORT_ID) to paypal_ipn_orders.SORT_ID</li> </ol> <p>I know how to cross reference the tables to find matching records when they are identical however not all if not most item titles have a slight variance ie extra spaces, a number 4 instead of 1, a the or extra character. </p> <pre><code>Query UPDATE paypal_ipn_orders SET sort_num = (SELECT sort_id FROM itemkey WHERE itemkey.item = paypal_ipn_orders.item_name) WHERE LOWER(payment_status) = 'completed' </code></pre> <p>Result</p> <pre><code>table: paypal_ipn_orders ITEM SORT_ID 4 Icy Manipulator ~ Ice Age NULL 4 Worldslayer - Mirrodin MtG Magic NULL 1 Karn Liberated - New Phyrexia MtG NULL 4 Blightning ~ Shards 12334234(identical title= a non-null SORT_ID) table: item_key ITEM SORT_ID 1 Icy Manipulator + Ice Age 12334231(doesnt exactly match) 4 Worldslayer - Mirrodin Magic 12334332(doesnt exactly match) 4 Karn Liberated - Phyrexia MtG 12334333(doesnt exactly match) 4 Blightning ~ Shards 12334234(perfect match) </code></pre> <p>Desired Result</p> <pre><code>table: paypal_ipn_orders ITEM SORT_ID 4 Icy Manipulator ~ Ice Age 12334231(similar title = match assign value) 4 Worldslayer - Mirrodin MtG Magic 12334232(similar title = match assign value) 1 Karn Liberated - New Phyrexia MtG 12334233(similar title = match assign value) 4 Blightning ~ Shards 12334234(exact title = match assign value) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1542036
    1. USuser1542036
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql select similar values when records match in 2 tables
      1. USsel
      1. VTFavorite
    2. VO
      singulars
      1. POmysql select similar values when records match in 2 tables
      1. USuser1542036
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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