Update Value Based On Value In Another Table

Question

I am looking to modify the value of a column on one table based on the condition of the value of a column of another table.

I am using the code:

UPDATE ORDERDETAIL
SET ORDERDETAIL.PRODUCTID = 'BASE1'
WHERE CUSTOMER.FIRSTNAME = 'JANE' AND CUSTOMER.LASTNAME = 'DOE';

This should be very straightforward, where am I going wrong?

are you able to run this without getting any errors? Apart from that, Looks like you are missing join between order detail and customer.Do u have any customer Id column in order details table? — Madhusudana Reddy Sunnapu, Feb 13 '16 at 07:54

score 0 · Answer 1 · answered Feb 13 '16 at 07:55

0

You have not specified the second TABLE explicitly. Join should work. Remember to always denote the alias rather than the table name right next to UPDATE

UPDATE O_T
SET PRODUCTID = 'BASE1'
from ORDERDETAIL O_T
JOIN CUSTOMER C_T
ON O_T.SOME_COLUMN_NAME=C_T.SOME_COLUMN_NAME
WHERE C_T.FIRSTNAME = 'JANE' AND C_T.LASTNAME = 'DOE';

answered Feb 13 '16 at 07:55

Chendur

1,099
1
11
23

Still Errors: Error starting at line : 1 in command - UPDATE O_T SET PRODUCTID = 'BASE1' from ORDERDETAIL O_T JOIN CUSTOMER C_T ON O_T.SOME_COLUMN_NAME=C_T.SOME_COLUMN_NAME WHERE C_T.FIRSTNAME = 'JANE' AND C_T.LASTNAME = 'DOE' Error at Command Line : 3 Column : 1 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: – Matt Johnson Feb 13 '16 at 08:11
ON O_T.SOME_COLUMN_NAME=C_T.SOME_COLUMN_NAME You got to change the "SOME_COLUMN_NAME" to the respective column in your table. I have given SOME_COLUMN_NAME because I cannot see your table structure. – Chendur Feb 13 '16 at 08:18
Yes, I did correct that code: UPDATE O_T SET PRODUCTID = 'BASE1' FROM ORDERDETAIL O_T INNER JOIN CUSTOMER C_T ON O_T.PRODUCTID=C_T.CUSTOMERID WHERE C_T.FIRSTNAME = 'JANE' AND C_T.LASTNAME = 'DOE'; But it still fails. Am I missing your point somewhere? I'm sorry. – Matt Johnson Feb 13 '16 at 08:24
1

Can you post the error here? A screenshot with your table structure and error would help too – Chendur Feb 13 '16 at 08:33
Evil333 it still errors out: Error starting at line : 1 in command - UPDATE O_T SET PRODUCTID = 'BASE1' from ORDERDETAIL O_T JOIN CUSTOMER C_T ON O_T.SOME_COLUMN_NAME=C_T.SOME_COLUMN_NAME WHERE C_T.FIRSTNAME = 'JANE' AND C_T.LASTNAME = 'DOE' Error at Command Line : 3 Column : 1 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: – Matt Johnson Feb 13 '16 at 09:04
This approach is not valid in Oracle, [which does not let you have a join in the update statement](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm#i2112182). – Alex Poole Feb 13 '16 at 09:18

score 0 · Answer 2 · edited May 23 '17 at 10:32

If you are always using a fixed value, you an use exists to find records which match the condition in the other tables:

UPDATE ORDERDETAIL OD
SET OD.PRODUCTID = 'BASE1'
WHERE EXISTS (
  SELECT NULL
  FROM CUSTOMER C
  JOIN ORDERS O ON O.CUSTOMERID = C.CUSTOMERID
  WHERE C.FIRSTNAME = 'JANE'
  AND C.LASTNAME = 'DOE'
  AND O.ORDERID = OD.ORDERID
);

Or a subquery:

UPDATE ORDERDETAIL OD
SET OD.PRODUCTID = 'BASE1'
WHERE OD.ORDERID IN (
  SELECT O.ORDERID
  FROM CUSTOMER C
  JOIN ORDERS O ON O.CUSTOMERID = C.CUSTOMERID
  WHERE C.FIRSTNAME = 'JANE'
  AND C.LASTNAME = 'DOE'
);

Oracle doesn't allow you to have a join directly in the update statement. If you were getting the new value from the other table as well you would something like this.

Update Value Based On Value In Another Table

2 Answers2