Home » SQL & PL/SQL » SQL & PL/SQL » update query
update query [message #305514] Tue, 11 March 2008 02:27 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
update 
	r_m_brnch 
set 
	brnch_dt_of_brnch_opn=
	(
select
		a.brnch_dt_of_brnch_opn
	from
		r_m_brnch a,
		r_m_t_transfer b
	where
		(trim(a.brnch_bk_cd),trim(a.brnch_brnch_no))
		IN
		  (select
			trim(c.s1),trim(c.s2)
		  from
			sample c,
			r_m_t_transfer b
		  where
			c.bname=b.brnch_name and b.ucnnew is not NULL
                       		  )
		and a.brnch_name=b.brnch_name
		and b.effective_date>a.brnch_dt_of_brnch_opn AND ROWNUM=1
)
where
	(trim(brnch_bk_cd),trim(brnch_brnch_no)) 
		IN 
		(select
                   trim(d.u1),trim(d.u2)
           from
                   sample3 d,r_m_brnch a
           where
                   trim(d.u1)=trim(brnch_bk_Cd)
                   AND trim(d.u2)=trim(brnch_brnch_no)
                   and d.bname=brnch_name AND ROWNUM=1);




while updating only one reord is updating the two sub queries returning multiple records can i update multiple records with same update query.

[Updated on: Tue, 11 March 2008 02:31]

Report message to a moderator

Re: update query [message #305518 is a reply to message #305514] Tue, 11 March 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can update multiple records but one by one. For each record of the source, you can have only one value (per updated column).

Regards
Michel
Re: update query [message #305519 is a reply to message #305518] Tue, 11 March 2008 02:53 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
that means i have to run this query in a loop or any other way i can update multiple records.
Re: update query [message #305528 is a reply to message #305519] Tue, 11 March 2008 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can update several records in one statement, it depends on what is selected by the "where" clause.

Regards
Michel
Re: update query [message #305532 is a reply to message #305514] Tue, 11 March 2008 03:43 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
can u please tell me in what section of my query i have to change in the way it will update all the records.

my senario is as follows.

first sub query is returning date and based upon the second sub query's brnch_bk_cd and brnch_brnch_no update date of opening of the bank and brnch of the result came out of second sub query


update r_m_brnch set brnch_dt_of_brnch_opn=(date obtained by this sub query) where (for branch and bank no obtained by this sub query)
Re: update query [message #305536 is a reply to message #305532] Tue, 11 March 2008 04:00 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I will not analyze in details your problem.
The principle is the following one:
- the subquery in "where" clause determines the rows you will update
- the subquery in the "set" clause determines the values with which you will update the selected rows.

Regards
Michel
Previous Topic: PLS-00103
Next Topic: What's wrong with this:PLS-00320
Goto Forum:
  


Current Time: Thu Dec 08 06:08:16 CST 2016

Total time taken to generate the page: 0.18500 seconds