Working with Sql Developer unable to update table

Question

Question.. create an SQL update statement which doubles the P_DISCOUNT for all products provided by vendors in TN or FL I have 2 tables and i'm trying to reference the vendor table which contains the states and the p_discount is located in the product table.

update PRODUCT
set P_DISCOUNT = 2 * P_DISCOUNT
where VENDOR.V_STATE in 
  (select VENDOR.V_STATE from VENDOR where  VENDOR.V_STATE = ('FL','TN'))

any suggestions would be great

Thank you,

You should really decide if you are using SQL Server or Oracle. These are very different databases. — Gordon Linoff
– Gordon Linoff, Commented Feb 22, 2015 at 18:31
What is the relationship between PRODUCT and VENDOR? What column joins these tables? — krivtom
– krivtom, Commented Feb 22, 2015 at 18:31

krivtom · Accepted Answer · 2015-02-22 18:35:21Z

2

Try this query:

update PRODUCT
set P_DISCOUNT = 2 * P_DISCOUNT
where V_CODE in 
    (select VENDOR.V_CODE from VENDOR where VENDOR.V_STATE IN ('FL','TN'))

You are filtering the condition based on joining field, not on any field in one of the tables

answered Feb 22, 2015 at 18:35

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kris Anderson Over a year ago

Error starting at line : 154 in command - update PRODUCT set P_DISCOUNT = 2 * P_DISCOUNT where V_CODE in (select VENDOR.V_CODE from VENDOR where VENDOR.V_STATE = ('FL','TN')) Error at Command Line : 157 Column : 59 Error report - SQL Error: ORA-01797: this operator must be followed by ANY or ALL 01797. 00000 - "this operator must be followed by ANY or ALL" *Cause: *Action:

krivtom Over a year ago

I updated the answer. It is IN operator that needs to be used

ranjan0487 · Accepted Answer · 2015-02-22 18:52:19Z

-1

update PRODUCT set P_DISCOUNT = 2 * P_DISCOUNT where V_STATE in
(select VENDOR.V_STATE from VENDOR where VENDOR.V_STATE in ('FL','TN'))

edited Feb 22, 2015 at 18:52

answered Feb 22, 2015 at 18:33

ranjan0487

258 bronze badges

9 Comments

Kris Anderson Over a year ago

get that error with thatError starting at line : 154 in command - update PRODUCT set P_DISCOUNT = 2 * P_DISCOUNT where VENDOR.V_STATE in (select VENDOR.V_STATE from VENDOR where VENDOR.V_STATE in ('FL','TN')) Error at Command Line : 156 Column : 7 Error report - SQL Error: ORA-00904: "VENDOR"."V_STATE": invalid identifier 00904. 00000 - "%s: invalid identifier"

Bob Jarvis - Слава Україні Over a year ago

Please explain how this "answer" improves on OP's query as stated in the question.

ranjan0487 Over a year ago

Does the table Product has the column V_STATE?

Bob Jarvis - Слава Україні Over a year ago

@user3572912 - it appears that you simply copied OP's query and posted it as an "answer". Please explain how this answers the question. Thanks.

Bob Jarvis - Слава Україні Over a year ago

Yes, I see now what you did. However, in my opinion this is not an answer. You need to explain the answer, not just post a query and expect people to figure out what you did.

|

Collectives™ on Stack Overflow

Working with Sql Developer unable to update table

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related