Solved

MySQL query to UPDATE, INSERT or DELETE

Posted on 2014-01-16
8
493 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 24

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 34

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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 24

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 108

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 108

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now