Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? (ORACLE 11G)
HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643229] Thu, 01 October 2015 06:55 Go to next message
ibnelyas
Messages: 5
Registered: October 2015
Junior Member
Dear Geeks,

Would like to know how to update a field in oracle based on simultaneous update in other fields

For eg.
table customers with fields customerid,qty,status

update customers set qty='10', status = decode(qty,'10','Open','Close') where customerid = 1

In this case the qty will be updated, if the updated qty matches user criteria, in this case it is 10, then status will be closed.

Please suggest if this is possible in simple query..

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643231 is a reply to message #643229] Thu, 01 October 2015 07:08 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Your SQL has the string '10' twice. Are you saying that both of these values are provided by the user? How? Or is there a fixed relationship between qty='10' and status='Open', and qty='anything else' and status='Close' ?

Incidentally:
are you sure of your data types? Is qty really a string?
Using phrases such as "Dear Geeks" does not make you friends on a professional forum.
Neither does using the word "field" when you mean "column".

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643232 is a reply to message #643229] Thu, 01 October 2015 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Test forum is NOT to post a real question but to test your post, is this what you wanted?
2/ Yes, you can update update several fields in one update separating each setting clause by a comma as you did
3/ Your statement "in this case it is 10, then status will be closed" is wrong, it is the opposite, if qty=10 then status is Open
4/ If qty is a number do not compare it to a string like '10' but to a number like 10.

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643233 is a reply to message #643232] Thu, 01 October 2015 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, the "qty" in the second part of "set" clause refers to the qty BEFORE your UPDATE statement and so do not take into account the modification made by the first part of "set" clause.

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643234 is a reply to message #643233] Thu, 01 October 2015 07:18 Go to previous messageGo to next message
ibnelyas
Messages: 5
Registered: October 2015
Junior Member
Michel Cadot ,

Its just a sample scenario of my requirement, as you mentioned my query is while updating the data, the second "qty" must consider the updated qty..
Is it possible.. ?

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643235 is a reply to message #643234] Thu, 01 October 2015 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not in this way, but as you know you put 10 in the first then you know the second part DECODE result is Open.
You have to give something that better represents your issue as here there is no problem: remove the DECODE in second part and just "status='Open'".

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643236 is a reply to message #643235] Thu, 01 October 2015 07:37 Go to previous messageGo to next message
ibnelyas
Messages: 5
Registered: October 2015
Junior Member
Michael consider the below query to describe my scenario..


UPDATE DELIVERY_DETAILS SET MOT = 'TRUCK', STATUS = CASE WHEN QTY_RECEIVED IS NOT NULL AND MOT IS NOT NULL THEN 'OPEN' WHEN MOT IS NOT NULL AND QTY_RECEIVED IS NULL THEN 'PARTIAL' ELSE 'CLOSED' END WHERE DO_ID = 1

ASSUMING QTY_RECEIVED IS ALREADY UPDATED FOR THIS DO_ID ,IN THIS CASE AFTER THE UPDATE, I NEED THE STATUS TO BE 'CLOSED'

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643238 is a reply to message #643236] Thu, 01 October 2015 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand what it is, what it is not, what it should be in the end.
Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

And format properly your statement, if you don't know how to do it, learn it using SQL Formatter.

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643239 is a reply to message #643238] Thu, 01 October 2015 09:04 Go to previous messageGo to next message
ibnelyas
Messages: 5
Registered: October 2015
Junior Member
please bear with me..

this is the table script and i have formatted the query using formatter..

CREATE TABLE delivery_details
(
do_id NUMBER,
qty_received VARCHAR2(3),
mot VARCHAR2(20),
status VARCHAR2(10)
)
/


ALTER TABLE delivery_details ADD (
CONSTRAINT delivery_details_PK
PRIMARY KEY
(do_id)
ENABLE VALIDATE)

the table has one row

do_id = 1, qty_received = 'YES',MOT = null, status = null

below is the update query..

UPDATE delivery_details
SET mot = 'TRUCK',
status = CASE
WHEN qty_received IS NOT NULL
AND mot IS NOT NULL THEN 'CLOSED'
WHEN mot IS NOT NULL
AND qty_received IS NULL THEN 'PARTIAL'
ELSE 'OPEN'
END
WHERE do_id = 1

so here i am updating mot and table must have the record has

do_id = 1, qty_received = 'YES',mot ='TRUCK',status = 'CLOSED'

hope you get my point

Report message to a moderator

Re: HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643241 is a reply to message #643239] Thu, 01 October 2015 09:17 Go to previous message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
So first off - the case statement will be checking the current values before the SET is applied so as far it's concerning in this MOT is null.
You need to check the value you're setting MOT to.
In your example you've hard-coded MOT. If that's what you're really doing then you just need to alter the CASE statement to assume mot is not null.
If on the other hand you're really using a variable in place of 'TRUCK' then you should alter the case to refer to the variable in place of the mot column.

Report message to a moderator

Previous Topic: Check Constraints having alphanumeric
Next Topic: insert data into column same table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 03:14:11 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.