OraFAQ Forum: SQL & PL/SQL » syntax of update stmt with literals in subquery

Home » SQL & PL/SQL » SQL & PL/SQL » syntax of update stmt with literals in subquery (Oracle 9i,solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

syntax of update stmt with literals in subquery [message #340547]

Wed, 13 August 2008 04:37

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'
)

Report message to a moderator

Re: syntax of update stmt with literals in subquery [message #340549 is a reply to message #340547]

Wed, 13 August 2008 04:42

Frank
Messages: 7901
Registered: March 2000

Senior Member

where a = 'abc'
and b = 'def'
and c = 'ghi'

or use a dummy select from dual.

Report message to a moderator

Re: syntax of update stmt with literals in subquery [message #340551 is a reply to message #340547]

Wed, 13 August 2008 04:47

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;

Report message to a moderator

Re: syntax of update stmt with literals in subquery [message #340560 is a reply to message #340547]

Wed, 13 August 2008 05:12

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: syntax of update stmt with literals in subquery [message #340587 is a reply to message #340547]

Wed, 13 August 2008 06:17

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')
)

Report message to a moderator

Re: syntax of update stmt with literals in subquery [message #340597 is a reply to message #340587]

Wed, 13 August 2008 06:31

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Partition Table
Next Topic:	FORALL INSERT loop break conditionally

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 18:34:08 CST 2024