php – Alternative to ERASE -> suspend -> UPDATE to keep updated records in the MySQL database

I've researched this a bit and I'm still confused as to the best practices to achieve something like this.

I am simply trying to maintain a Product attribute synchronized table with an external URL where the Product attribute the information can change

This is what my table looks like now:

║ Attribute ║ productID ║ last Modified od created ║

I'm not sure if this is ideal but I have a double Primary key (as the attribute values ​​themselves are not usually unique, but Attribute <-> Product ID the value should be)

Primary keys

Attribute, productID

and a External key index in Product ID to link it to other tables


Product ID

As I've been doing, using DELETE, UPDATE (in a class for now it's only called Database):

public function deleteProdTable ($ table, $ fk_id)
$ this-> data = $ this-> db-> run ("DELETE FROM`". $ table. "` WHERE` productID` =? ", [$fk_id]);

returns $ this-> data;


$ datasql = new Database ();
$ fk_id = 555;
$ delattr = $ datasql-> deleteProdTable (& # 39; productattributes & # 39 ;, $ fk_id);
// Apparent side effect of the DELETE-> UPDATE method, if I do not sleep for a couple of seconds after DELETE and before INSERT, INSERT may not work correctly
sleep (2);

and my insertion:

            public function insertProductAttrs ($ productattribute, $ fk_id)
$ this-> data = $ this-> db-> run ("INSERT INTO` productattributes` (` Attribute`) VALUES (?) WHERE `productID` =?", [$productattribute, $fk_id]);

returns $ this-> insertprod;

This works well in conjunction with the previous DELETE query (with the added intermediate dream as commented), but I think there should be a better method.

The only other two methods I've thought about have been modifying my table and adding a state Column to my table

╔═══════════╦═══════════╦═════════╦══════════════╦ ═════════╗
║ Attribute ║ productID ║ status ║ lastModified ║ created ║
╚═══════════╩═══════════╩═════════╩══════════════╩ ═════════╝

And modify my Insert function to make a reference of left union of temporary table against the existing table, marking one if it exists in both tables, 0 if the attribute is not included in the most recent call (it only exists in the non-temporary table).

Something like:

            public function insertProductAttr ($ prodAttributesArr, $ fk_id)
$ this-> db-> run ("CREATE TEMPORARY TABLE productattributes_temp AS SELECT * FROM productattributes LIMIT 0");

$ this-> data = $ this-> db-> run ("INSERT INTO` productattributes` (` Attribute`) VALUES (?) WHERE `productID` =?", [$productattribute, $fk_id]);

$ this-> data = $ this-> db-> run ("UPDATE` productattributes` pa LEFT JOIN` productattributes_temp` pa_t in pa.productID = pa_t.productID SET pa.Attribute = COALESCE (pa.Attribute, pa_t.Attribute) , pa.status = CASE WHEN pa_t.status IS NULL THEN 0 ELSE 1 END; ");

I plan to add try capture Declarations in production, but at this moment I'm just focused on discovering the best method to achieve an always updated database.

the state column could take it or leave it, I'm using another MySQL audit table, so old Attributes (In the case of the temporary table, the marked state of the row 0), they are not too important.

Apart from this, I thought about doing something like INSERT IN ... DUPLICATE KEY UPDATE, and this works to include new rows or update existing ones, but does not take into account the deletion of old attributes when necessary.

Am I missing something? Or … and I'm not really sure that this is true, since there are many recommendations against, my current DELETE-> sleep-> UPDATE The methodology of the best way to take?