Mysql how to update multiple records without specifying foreignkey value

Question

I have a tables that looks like the following:

Table - order
id purchase_order_item_id amount
1  1324                   0.0
2  2435                   50.2
3  5643                   87.2
4  6475                   0.0

Table - purchase_order_item
id                     item_id
1324                   82
2435                   83
5643                   84
6475                   85

Table - item
id                     amount
82                   76.1
83                   50.2
84                   87.2
85                   65.9

Now I want to check if Order table is having value 0.0 for amount. In this case I have to get amount data from table item (Order->purchase_order_item->item) and update in order table.

I have written below update query which is not running.Trying to correct it.

UPDATE order
INNER JOIN purchase_order_item ON purchase_order_item.id =     order.purchase_order_item_id
SET 
order.amount= 
(
SELECT item.amount from item
INNER JOIN purchase_order_item ON purchase_order_item.item_id=item.id
)
where order.amount=0.0;

I am new in mysql query and trying to write correct update query.

Replace tables structures descriptions with their CREATE TABLE scripts. Give some example data as INSERT INTO scripts. Show desired result for this sample data as table-formatted text. — Akina, Mar 25 '20 at 13:05
what exact columns are linked between table2 and table3 via foreign key? Re-write your question in a clear example and provide sample data. — t1f, Mar 25 '20 at 13:07
@t1f There is a foreignkey exists in table T1 which links T1 with T2. — S Singh, Mar 25 '20 at 13:11
T2.t2id is not a valid column name please add the create table scripts.(show create table ) — P.Salmon, Mar 25 '20 at 13:14
Do you have some aversion to providing the create table scripts? Voting to close. — P.Salmon, Mar 25 '20 at 13:44
@P.Salmon I am sorry for not to providing sufficient details. Adding shortly. — S Singh, Mar 25 '20 at 13:48
Order is a reserved word please review https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql and if there is an error in your query show it don't just say 'which is not running' AND do you really have purchase_order_item with no quantity and only ever 1 per order? — P.Salmon, Mar 25 '20 at 14:31

GMB · Accepted Answer · 2020-03-25T14:45:39.290

1

I think that you want the update ... join syntax:

update orders o
inner join purchase_order_items poi on poi.id = p.purchas_order_item_id
inner join items i on i.id = poi.item_id
set o.amount = i.amount
where o.amount = 0

Side note: order is a reserved word in MySQL (as for other databases), so I renamed the table to orders (I pluralized other table names too to make it consistent).

edited Mar 25 '20 at 14:45

answered Mar 25 '20 at 14:27

GMB

216,147
25
84
135

Err ORDER is a reserved word backticks required. – P.Salmon Mar 25 '20 at 14:29
@P.Salmon: I usually take the table names as quite hypothetical, but sure you are right. I modified my answer. – GMB Mar 25 '20 at 14:46
@GMB Your query doesn't give any error but it doesn't update records. I am getting message. # MySQL returned an empty result set (i.e. zero rows). – S Singh Mar 25 '20 at 14:58
1

@GMB Although I am getting message. # MySQL returned an empty result set (i.e. zero rows) but when I look into table field values, it actually updates. – S Singh Mar 25 '20 at 15:08

Mysql how to update multiple records without specifying foreignkey value

1 Answers1