Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE MULTIPLE ROWS IN ONE UPDATE PASS (Oracle 11g, Win7)
UPDATE MULTIPLE ROWS IN ONE UPDATE PASS [message #604272] Fri, 27 December 2013 02:55 Go to next message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior Member
create table update_test
(
	company varchar2(50),
	companyid varchar2(20),
	prodid varchar2(20),
	flag varchar2(10)	
)

insert into update_test values ('centOSX','C11','21X','T');
insert into update_test values ('centOSX','C11','25X','T');
insert into update_test values ('centOSX','C11','29X','T');

insert into update_test values ('dosOSX','d19','21HH','F');
insert into update_test values ('dosOSX','d19','25HH','F');
insert into update_test values ('dosOSX','d19','29HH','F');

insert into update_test values ('asusOSX','A11','21X','T');
insert into update_test values ('asusOSX','A11','25X','T');
insert into update_test values ('asusOSX','A11','29X','T');

insert into update_test values ('legoOSX','L11','21X','T');
insert into update_test values ('legoOSX','L11','25X','T');
insert into update_test values ('legoOSX','L11','29X','T');


is it possible in one update pass(STATEMENT) to update the flag of dosOSX to 'T' and make other company's flag set to 'F'. We can do it using two update passes but is it possible in any wayo to do the same with one SQL statement.

Thanks!
Re: UPDATE MULTIPLE ROWS IN ONE UPDATE PASS [message #604273 is a reply to message #604272] Fri, 27 December 2013 03:14 Go to previous messageGo to next message
tigsav
Messages: 44
Registered: April 2012
Member
UPDATE UPDATE_TEST SET FLAG = 
CASE WHEN COMPANY IN ('dosOSX') THEN 'T'
     ELSE 'F'
     END;
Re: UPDATE MULTIPLE ROWS IN ONE UPDATE PASS [message #604288 is a reply to message #604273] Fri, 27 December 2013 11:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
No need to use keyword "IN", an equal operator(=) would suffice.
Re: UPDATE MULTIPLE ROWS IN ONE UPDATE PASS [message #604340 is a reply to message #604288] Sun, 29 December 2013 00:11 Go to previous message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior Member
Thank you for the replies.

Done!
Previous Topic: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column
Next Topic: move data from one table to another table
Goto Forum:
  


Current Time: Sat Dec 20 17:22:28 CST 2014

Total time taken to generate the page: 0.31800 seconds