Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL query to UPDATE, INSERT or DELETE

Posted on 2014-01-16
8
Medium Priority
?
512 Views
Last Modified: 2014-01-16
I know of a brute force way to do this, but it struck me that there might be an elegant way in MySQL to get this done. I have a database with 4 columns

SKU (primary) Qty Price Weight

Open in new window


Then once a day I receive a text fill with the update SKU and Qty list. There may be new SKUs are SKUs that no longer exist. So there are three conditions:

1. If SKU already exists in DB, just update Qty
2. If SKU does not exist in the DB, add it and the Qty with Price and Weight blank
3. If SKU exists but is not in text file, delete the row

Here is an example:

SKU (primary) Qty Price Weight
123 12 19.99 0.3
234 24 9.95 1.3
345 48 89.98 3.5

Open in new window


The text file would be

SKU (primary) Qty
123 12
234 19
456 36

Open in new window


So the new database should be

SKU (primary) Qty Price Weight
123 12 19.99 0.3
234 199.95 1.3
456 36

Open in new window


I hope that makes sense. Or maybe there is not a single query that will nicely do all of this.
0
Comment
Question by:sypder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39786893
I think the easiest thing you can do is to delete all data from your table and replace it with the data from the file. You may need to consider creating a backup before that
0
 
LVL 3

Author Comment

by:sypder
ID: 39786898
Yeah, but the file does not have the Price and Weight columns, so I want to keep that data if it exists.
0
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 400 total points
ID: 39786914
You need an
INSERT INTO table-name (SKU,Qty) VALUES (123,12), (234, 19) etc
  ON DUPLICATE KEY UPDATE Qty=VALUES(Qty);

HTH,
Dan
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 58

Assisted Solution

by:Gary
Gary earned 400 total points
ID: 39786925
Dan's got the sql spot on
For the delete add a marker column to the table that you can reset to 0 prior to the insert/update
Within the insert/update change to 1
When that's finished just do a delete where the col=0

There is no one for all way of doing it.
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 400 total points
ID: 39786934
@Dan Craciun: your solution does not delete the records that are not in the file.

How about this. You import the data from the file to a new temp table.
Then you use a DELETE statement, like this:
DELETE FROM table-name WHERE SKU NOT IN (SELECT SKU FROM temp-table)

Open in new window

Then you use the INSERT statement similar to Dan's:
INSERT INTO table-name (SKU,Qty) 
SELECT SKU,Qty FROM temp-table
  ON DUPLICATE KEY UPDATE Qty=temp-table.Qty;

Open in new window

0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 800 total points
ID: 39787081
Please see http://www.laprbass.com/RAY_temp_sypder.php

Using a default value of -1 allows a quick way of identifying the new / missing information.

<?php // RAY_temp_sypder.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28340788.html


// TEST DATA BASE INFORMATION WITH SKU (primary) Qty Price Weight
$test_data_arrays = array
( array( 'sku' => 123, 'qty' => 12, 'price' => 19.99, 'weight' => 0.3)
, array( 'sku' => 234, 'qty' => 24, 'price' =>  9.95, 'weight' => 1.3)
, array( 'sku' => 345, 'qty' => 48, 'price' => 89.98, 'weight' => 3.5)
)
;

// SIMULATED FILE WITH SKU AND QTY LIKE FROM file_get_contents()
$test_file = <<<EOD
123 12
234 19
456 36
EOD;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( sku    INT           NOT NULL PRIMARY KEY DEFAULT -1
, qty    INT           NOT NULL DEFAULT -1
, price  DECIMAL(10,3) NOT NULL DEFAULT -1.0
, weight DECIMAL(10,3) NOT NULL DEFAULT -1.0
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
foreach ($test_data_arrays as $datas)
{
    // CONSTRUCT THE QUERY USING UNESCAPED VARIABLES, SINCE THESE ARE ALL NUMBERS
    $sql = "INSERT INTO my_table ( sku, qty, price, weight ) VALUES ( {$datas['sku']}, {$datas['qty']}, {$datas['price']}, {$datas['weight']} )";
    echo PHP_EOL . $sql;

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}
echo PHP_EOL;


// PRETEND THAT WE READ THE EXTERNAL FILE, AND MAKE AN ARRAY OF LINES
$test_lines = explode(PHP_EOL, $test_file);

// CREATE AN ARRAY OF SKUS FOR THE DELETIONS
foreach ($test_lines as $str)
{
    $temp    = explode(' ', $str);
    $goods[] = $temp[0];
}

// TURN THE ARRAY INTO A QUERY FRAGMENT
$good = implode(',', $goods);

// REMOVE THE UNWANTED ROWS
$sql = "DELETE FROM my_table WHERE sku NOT IN ($good)";
echo PHP_EOL . $sql;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// TURN THE INPUT FILE INTO A QUERY FRAGMENT
$vals = NULL;
foreach ($test_lines as $str)
{
    $temp = explode(' ', $str);
    $vals .= '(' . $temp[0] . ',' . $temp[1] . '),';
}
$vals = rtrim($vals, ',');

// ADD OR UPDATE THE ROWS WITH THE DATA FROM THE FILE
$sql = "INSERT INTO my_table (sku, qty) VALUES $vals ON DUPLICATE KEY UPDATE qty = VALUES(qty)";
echo PHP_EOL . $sql;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// SHOW THE WORK PRODUCT
$sql = "SELECT * FROM my_table ORDER BY sku";
echo PHP_EOL . $sql;
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

while ($row = $res->fetch_object())
{
    echo PHP_EOL;
    print_r($row);
}

Open in new window

Best to all, ~Ray
0
 
LVL 3

Author Comment

by:sypder
ID: 39787327
Everyone, thanks. This looks perfect. I find it important to do these simple tasks well and this looks nice.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39787381
Simple tasks done well are the foundation of all civilizations.  Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question