Select Query > Update Query > Table Field

[tuggleport]

I have found myself in a bad situation.



Our programmer fell in love and hasn't been very helpful since then (He's not even in the country).

I am trying to get a query generated quantity to update into our products table.

What I have so far come up with "Operation must use an updateable query" so methinks there is much more I need to do. This is what I have so far:

UPDATE Products INNER JOIN InventoryOnHand ON Products.ProductCode = InventoryOnHand.ProductCode SET InventoryOnHand.QtyOnHand = "Products:InStock";

[Rixxe]

I think you need something similar to this:

UPDATE suppliers SET supplier_name =
(
SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id
)

[Anita]

Hi I am stuck with a query
I want to update a "price" field in the "tblBurger" which is total price of all ingredients used + 0.70 cents
for that I used select statement to get the total cost price of all the ingredients then I added 0.70 cents
but I am not able to update it in my tblBurger price field.

I get following error
-------
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tblBurger.burgerDesc = 'desiBurger'' at line 22
---------

Structures
==========

tblBurger

Field	Type	Null	Key	Default	Extra
burgerID	tinyint(4)	NO	PRI	NULL	auto_increment
burgerDesc	varchar(30)	YES		NULL
price	decimal(4,2)	YES		NULL
noSales	smallint(5) unsigned	YES		NULL

==========

tblBurgering

Field	Type	Null	Key	Default	Extra
burgerID	tinyint(4)	NO	PRI	0
ingID	tinyint(4)	NO	PRI	0

===============
tblIngredient

Field	Type	Null	Key	Default	Extra
ingID	tinyint(4)	NO	PRI	NULL	auto_increment
ingDesc	varchar(20)	YES		NULL
cost	decimal(4,2)	YES		NULL

===============



UPDATE tblBurger
SET price =
(
SELECT CAST(
(
SELECT SUM(t.cost)
FROM tblBurger AS r, tblBurgering AS g, tblIngredient AS t
WHERE r.burgerID = g.burgerID
AND g.ingID = t.ingID
AND r.burgerDesc = "desiBurger"
) as DECIMAL(4,2)
) + 0.70


FROM tblBurger


WHERE `tblBurger`.`burgerDesc` = 'desiBurger'
)


WHERE tblBurger.burgerDesc = 'desiBurger'