Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL query to UPDATE, INSERT or DELETE

Posted on 2014-01-16
8
Medium Priority
?
514 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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 dynamically set the form action using jQuery.
Suggested Courses

885 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