HOW TO UPDATE ONE FIELD IN A TABLE BASED ON OTHER ? [message #643229] |
Thu, 01 October 2015 06:55 |
|
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..
|
|
|
|
|
|
|
|
|
|
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 |
|
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
|
|
|
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 |
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.
|
|
|