Solved

MySQL query to UPDATE, INSERT or DELETE

Posted on 2014-01-16
8
510 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 100 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 58

Assisted Solution

by:Gary
Gary earned 100 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 100 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 110

Accepted Solution

by:
Ray Paseur earned 200 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 110

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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

628 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