Home » SQL & PL/SQL » SQL & PL/SQL » syntax of update stmt with literals in subquery (Oracle 9i,solaris)
syntax of update stmt with literals in subquery [message #340547] Wed, 13 August 2008 04:37 Go to next message
manoj339
Messages: 9
Registered: April 2007
Junior Member
I want to use below update stmt,but need the correct syntax for the below subquery.Pl suggest the correct syntax if I want to use hard coded values of the 3 columns in the subquery.


update trd_arch_partition_ref
set archive_dt = to_date('20080808','yyyymmdd')
where (sys_id,src_sys_asgn_ref_no,br_cod) in (
'abc',
'def'
)


Re: syntax of update stmt with literals in subquery [message #340549 is a reply to message #340547] Wed, 13 August 2008 04:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
where a = 'abc'
and b = 'def'
and c = 'ghi'

or use a dummy select from dual.
Re: syntax of update stmt with literals in subquery [message #340551 is a reply to message #340547] Wed, 13 August 2008 04:47 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
This sort of thing seems to work:

create table x (a number, b varchar2(20), c number);

insert into x values (1,'z',3);

update x set c=7 where (a,b) in ((1,'z'),(2,'m'));

select * from x;
Re: syntax of update stmt with literals in subquery [message #340560 is a reply to message #340547] Wed, 13 August 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I see 3 columns and 2 values.
More, it should not be 3 values but a list of a list of 3 values.

Regards
Michel
Re: syntax of update stmt with literals in subquery [message #340587 is a reply to message #340547] Wed, 13 August 2008 06:17 Go to previous messageGo to next message
manoj339
Messages: 9
Registered: April 2007
Junior Member
Thanks to all those who replied.The solution mentioned has worked out.

Your soln:
update trd_arch_partition_ref
set archive_dt = to_date('20080808','yyyymmdd')
where (sys_id,src_sys_asgn_ref_no,br_cod) in (
('a',b','c'),
('d','e','f'),
('x','y','z')
)

Re: syntax of update stmt with literals in subquery [message #340597 is a reply to message #340587] Wed, 13 August 2008 06:31 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Partition Table
Next Topic: FORALL INSERT loop break conditionally
Goto Forum:
  


Current Time: Sat Dec 10 18:29:55 CST 2016

Total time taken to generate the page: 0.04183 seconds