0

how can we update a column from multiple table in a single query? i tries like shown below but not success.

update orders o
inner join status s on o.order_status_id = s.status_id
set o.ords_stas_id='23' 
where o.location='DRS'
and s.stas_enable = '0';

[table] (https://i.stack.imgur.com/Sjccj.png) Any suggestion?

Haruya
  • 15
  • 4
  • Please don't ever just say "it doesn't work" or "no success". Tell us what happens exactly. I suppose that you get an error message when running your statement. So, which error message do you get? – Thorsten Kettner May 08 '23 at 08:57

2 Answers2

1

You want to update all orders with location DRS and sta_enable = 0. One table to update, two conditions in the WHERE clause:

update orders o
set o.ords_stas_id = 23
where o.location = 'DRS'
and o.order_status_id in (select s.status_id from status s where s.stas_enable = 0);

Just in order to mention it: Sometimes we do have a query the joins of which both lead to the rows to update and the value to set. In that case we apply the update on the query. (This is not the case in your statement, though; we already know that we must set the id to 23. It would be the case if we got the id to set in the order table from the status table row.)

We could write your update statement thus:

update
(
  select ords_stas_id as value, 23 as new_value 
  from orders o
  inner join status s on o.order_status_id = s.status_id
  set o.ords_stas_id = '23' 
  where o.location = 'DRS'
  and s.stas_enable = 0
)
set value = new_value;

which would do the same thing as my query above, only in a more complicated way. But if we wanted to use a value from the status table, e.g. replace 23 as new_value by s.some_id as new_value, then this would be the appropriate way to write the update statement.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

Update using join doesn't work in oracle. You need a merge statement -

MERGE INTO orders o
USING (SELECT * FROM status
        WHERE stas_enable = '0') s
ON (o.order_status_id = s.status_id)
WHEN MATCHED THEN
             UPDATE
                SET o.ords_stas_id='23'
              WHERE o.location='DRS';
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • error: near "o": syntax error, not working – Haruya May 08 '23 at 06:19
  • Haruya, are you sure you're using **Oracle**? Because, error message you posted in comment doesn't sound like Oracle. There's no "near" in its messages. Could you post what database **exactly** returned? **Ankit**, that's an invalid statement (missing INTO keyword, AND should be put into WHERE clause) so ... merge might be an answer, but not with your syntax. – Littlefoot May 08 '23 at 07:38