[MySQL] ON DUPLICATE KEY

[gkm]

If you want to clean out a table inside a transaction, truncate is the fastest option. If you do not need a transaction and you system can survive the table temporarily disappearing, then you can use the drop/create combo. Most systems do not like this brief table disappearing act, hence why I would recommend truncate as the goto solution. You can also use a delete statement without a where clause, but since that logs the individual row deletes, the database has to work harder and it is therefore slower than truncate.

But in you specific case I would recommend the insert/update solution discussed extensively in this thread.

 

[koeksGHT]

Also add indexes on any fields if you join tables else slow.

 

[Thor]

Guys this is now the time when I need a professional to step in.

According to this:
http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

Truncate, drop, delete, create etc all of the DDl's creates an explicit commit.

But reading here:
http://dev.mysql.com/doc/refman/5.7/en/commit.html

It says: "By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back."

And: "To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:"

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Thus... My pdo way should work perfectly?

<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema and data */
$sth = $dbh->exec("TRUNCATE TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'");

/* Recognize mistake and roll back changes */
$dbh->rollBack();

/* Database connection is now back in autocommit mode */
?>

However the manuals is still confusing, because it says:

"Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships."

 

[Solarion]

Still trying to wrap my head around a simple update won't do the job ie If exists then Update else Insert New?

Once you start dropping rows you are going to affect every record in other table tied to that PK row.

 

[Thor]

Why is esquire included?

Might as well add Mustek/Rectron, Frontosa and Corex to the mix

 

[koeksGHT]

Why is esquire included?

Might as well add Mustek/Rectron, Frontosa and Corex to the mix

Having stroke?

 

[Thor]

Having stroke?

Dammit. I have too many tabs open

 

[Solarion]

Having stroke?

Coding problems do that too you after a while. Start swatting invisible flies and searching for muffins in the postbox.

 

[gkm]

Sorry, you are right, truncate is a DDL statement. I retract my advice to even consider truncate.

But as I mentioned previously, I still say use insert/update statements. Something like the following should do the trick:

INSERT INTO exchangerates (code,amount) VALUES ('ZAR',15.0)
  ON DUPLICATE KEY UPDATE amount=15.0;

 

[Solarion]

Sorry, you are right, truncate is a DDL statement. I retract my advice to even consider truncate.

But as I mentioned previously, I still say use insert/update statements. Something like the following should do the trick:

INSERT INTO exchangerates (code,amount) VALUES ('ZAR',15.0)
  ON DUPLICATE KEY UPDATE amount=15.0;

This ^

If your database is normalized correctly then that simply change automatically reflect on all your other records referencing that table.

 

[Thor]

This is helping quite a bit for anyone interested

https://www.khanacademy.org/computing/computer-programming/sql

 

[Thor]

This is now from the other side of the book, I now want to display the data from the DB

Now I have been working in for loops and while loops for the past 3 days so much so that I seem stuck and can't think of a lets call it "flat way" to do this query as my first instinct was loop it.

//Select currency to show USD
$sql = "SELECT currency_history FROM currency WHERE currency_code = 'USD'";

$statement = $db->prepare($sql);
$statement->execute();

//Insert the currency history into a variable

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  
  $currency_history_usd = $row["currency_history"];

}

//Select currency to show EUR
$sql = "SELECT currency_history FROM currency WHERE currency_code = 'EUR'";

$statement = $db->prepare($sql);
$statement->execute();

//Insert the currency history into a variable

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  
  $currency_history_eur = $row["currency_history"];

}

//Select currency to show GBP
$sql = "SELECT currency_history FROM currency WHERE currency_code = 'GBP'";

$statement = $db->prepare($sql);
$statement->execute();

//Insert the currency history into a variable

while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
  
  $currency_history_pound = $row["currency_history"];

}

Is it fine to do that (like the many ways to kill a cat saying in programming) or am I making some serious mistakes here?

 

[Thor]

Hey, look what I found.

 

[Johnatan56]

Hey, look what I found.

Some books you didn't return to the library? (The moon is down)

 

[DA-LION-619]

Some books you didn't return to the library? (The moon is down)

 

[Thor]

 

[Solarion]

Just downloaded that training kit & test.

Think I need to brush up a bit on my SQL some of those questions :erm:

 

[Thor]

Here is a pretty cool currency API -> http://fixer.io/