Incorrect Sql When Updating Table In Join

[Mitch]

Given the following query:

SELECT s.skuid, s.sku, s.sex, s.skuname, c.*

    FROM sku s LEFT JOIN sku_classification_sku c

    ON s.skuid = c.skuid

    WHERE c.skuclassificationskuid IS NULL;

 

 

This shows rows in sku where there is no corresponding row in sku_classification_sku.  I run this query and then put the results grid into update mode on table sku_classification_sku and then type entries in for table sku_classification_sku. SQLyog generates and executes UPDATE statements, but should be generating INSERT statements. (skuclassificationskuid is the PK for table sku_classification_sku.)

 

UPDATE `gf`.`sku_classification_sku` SET `skuid` = ‘2878’ , `classificationid` = ’13’ WHERE `skuclassificationskuid` IS NULL;

 

should be something like

 

INSERT INTO `gf`.`sku_classification_sku` SET `skuid` = ‘2878’ , `classificationid` = ’13’;

[Mitch]

Any feedback on this?

[Sibin A S]

Hello Mitch,

 

The data in the resultset is obtained after executing the query and not from the table.

 

In the resultset (join of two tables), UPDATE query is fired when you try to modify the value of an existing row. INSERT query is fired in case of a new row is added.

 

Regards,

Sibin

[Mitch]

I think SQLyog should be smart enough to understand that skuclassificationskuid is the primary key of the sku_classification_sku table, so an update with a null PK doesn’t make sense, so an insert is needed instead.

[Author]

Posts