MySQL "REPLACE INTO" using a SELECT to pull one field

Question

I've got a MySQL database that has two tables (actually many more). The first table links a product's SKU number to an arbitrary ID. There is a second table that records the End of Day inventory for each item based on this ID. When the inventory is changed for reasons OTHER than sales, there is a record placed in this second table with a Boolean set to false. This allows me to say that this new number is not valid as a vector for sales previous, but is for the next day's sales.

There is some syntax error in this code. I'm still a student, and would greatly appreciate the help in explaining how this kind of update would work. I know the first value needs to come from the 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 works like insert, except that if there is a row with the same key you are trying to insert, it will be deleted on replace instead of giving you an error.

You can either specify arguments directly:

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

You cannot however mix both types of syntax in one query.

But there is nothing stopping you from adding constant values as columns for the SELECT:

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

Answer 2

You're trying to do a replace into and a select in the same statement. If you're trying to run this using a program of some sort or simply inputting it into MySQL, if you place a semi-colon after the ) at the end of the values section, it will treat it as 2 separate requests.

If you're running this through PHP, then you'll need to break it up into 2 separate statements.

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