CODEWITHSUNDEEP
mysqlsqlview
user3955072
user3955072

Reputation:

Insert data from mysql view into a table where not exist

Table will not update when new data is populated in the view. The query runs with no error, but the table is not updated. I'm trying to update the table with data from the view that is not already in the table, based upon the shipping id.

INSERT INTO `table`(`store`, `shippingid`)
SELECT store,shipment_id FROM view WHERE NOT EXISTS (SELECT `shippingid` FROM `table`)

Upvotes: 0

Views: 77

Answers (2)

cosmos
cosmos

Reputation: 2303

If you have any row in your table this will always be false:

WHERE NOT EXISTS (SELECT `shippingid` FROM `table`)

It seems you might need to add correlation:

WHERE NOT EXISTS (SELECT `shippingid` FROM `table` WHERE shippingid = view.shipment_id)

Please note that, theoretically, sub-queries execute once for every row of outer query. So to understand the flow.. take some sample data and understand how the query will produce results.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You have to include the correlation between the view and your table:

INSERT INTO `table`(`store`, `shippingid`)
SELECT store,shipment_id 
FROM view 
WHERE NOT EXISTS (SELECT `shippingid` 
                  FROM `table` 
                  WHERE shippingid = view.shipment_id)

Please note that this query is an INSERT operation, not an UPDATE.

Upvotes: 1

Related Questions