MySQL "REPLACE IN" using SELECT to pull a single field

Question

I have a MySQL database in which there are two tables (actually many more). The first table associates the SKU of the product with an arbitrary identifier. There is a second table that records the end of day inventory for each item based on this identifier. When inventory changes for reasons other than sales, this second table contains a record with a boolean value of false. This allows me to say that this new number is not valid as a vector for sales of the previous one, but for sale the next day.

There is a syntax error in this code. I'm still a student and really appreciate the help in explaining how this type of update will work. I know the first value should come from a select statement?

Here is my current MySQL statement:

REPLACE INTO sales (`itemID`, `date`, `qty`, `price`) VALUES ([itemID], CURDATE(), [qty], 0.00) SELECT itemID FROM item WHERE `sku` = [sku] 

Answer 1

Replace jobs like insert, except that if there is a row with the same key that you are trying to insert, it will be deleted when replaced instead of giving you an error.

You can directly specify the arguments:

 REPLACE INTO sales( `item_id`, `date`, `qty`, `price` ) VALUES( 15, '2012-01-01`, 5, '120.00' ) 

or specify them using SELECT:

 REPLACE INTO sales( `item_id`, `date`, `qty`, `price` ) SELECT item_id, date, qty, price FROM sales_to_accept WHERE sales_id = 721 

However, you cannot mix both types of syntax in a single query.

But there is nothing to prevent you from adding constant values ​​as columns for SELECT:

 REPLACE INTO sales( `item_id`, `date`, `qty`, `price` ) SELECT item_id, CURDATE(), 5, '74.00' FROM item WHERE `sku` = 'something' 

Answer 2

You are trying to replace and select in the same expression. If you try to run this using some program or just paste it into MySQL, if you put a comma after ) at the end of the values ​​section, it will treat it as two separate queries.

If you use this via PHP, you need to break it down into 2 separate statements.

 REPLACE INTO sales (`itemID`, `date`, `qty`, `price`) VALUES ((SELECT itemID FROM item WHERE `sku` = [sku]), CURDATE(), [qty], 0.00)