Home » SQL & PL/SQL » SQL & PL/SQL » Update statement
Update statement [message #219308] Tue, 13 February 2007 23:19 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I have two tables TABLE_A and TABLE_B.

TABLE_B has four columns in a row representing phone_no,mobile_no,fax_no and email.

I have to update TABLE_A with these column values but the problem is that the TABLE_A has four rows corresponding to these four columns .
So, i have to update phone_no column value from TABLE_B to the row contaning phone_no data in TABLE_A and similarly mobile_no column value from TABLE_B to the row containing mobile_no data in TABLE_A and so on.
In reality ,i have to update column values from a single row to multiple rows.
I have written the following query but its giving me an error which says
(select f.abc,f.def 
	from (select con,abc,def 
		from (select d.contact_id con,
			     case 
				when r=1 then to_char(d.phone_no)
  				when r=2 then d.mobile_no
				when r=3 then d.fax_no 
				when r=4 then d.email end as abc ,
			     case 
				when r=1 then 'PHONE_NO'
				when r=2 then 'MOBILE_NO'
				when r=3 then 'FAX_NO'
			        when r=4 then 'EMAIL' end as def 
			from TABLE_B d,(select rownum r 
                                        from all_objects 
                                        where  rownum <=4 )				where d.contact_id =7011) e
              where e.abc is not null) f
where 
f.def = a.ADDRESS_EXTN_TYPE 
and a.ENTITY_TYPE='CONTACT' 
and a.address_type= 'OFFICE' 
and a.ENTITY_ID=7011)


ORA-01407: cannot update ("SCHEMA"."TABLE_A"."COLUMN_A") to NULL

Can any one suggest what is the problem ?
I am using Oracle 9i.

regards
Re: Update statement [message #219313 is a reply to message #219308] Tue, 13 February 2007 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>ORA-01407: cannot update ("SCHEMA"."TABLE_A"."COLUMN_A") to NULL
What you posted will NOT produce this error; so You're On Your Own (YOYO)!
Re: Update statement [message #219330 is a reply to message #219313] Wed, 14 February 2007 01:00 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi anacedent,
Thanks for your reply.
Even i am surprised about this error but this is coming.
May be there is some other issue.

Can anyone else help me about this error ?

Regards
Re: Update statement [message #219332 is a reply to message #219330] Wed, 14 February 2007 01:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us your COMPLETE statement. Now you only provided a select, which could never (well, almost never) lead to the error you showed.
Re: Update statement [message #219334 is a reply to message #219330] Wed, 14 February 2007 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What you posted is a SELECT statement. SELECT does not update a table, so - that's what Anacedent says - this is NOT a code which produced the error.

But, if this select statements is part of an UPDATE statement, that would be a different story. If so, make sure you are updating only those records which are to be updated - by default, it would set new column values for matching records and set all the others to NULL.

To avoid it, you might add additional WHERE EXISTS ... condition which would restrain records set to be affected by this update statement.

However, if it is not an UPDATE, what is it, then?
Re: Update statement [message #219336 is a reply to message #219334] Wed, 14 February 2007 01:12 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
I am sorry guys ,i missed out on first two lines ,work pressure may be. Embarassed

Here is the complete statement:


update TABLE_A a 
set (a.COLUMN_A,a.COLUMN_B) = 
(select f.abc,f.def 
	from (select con,abc,def 
		from (select d.contact_id con,
			     case 
				when r=1 then to_char(d.phone_no)
  				when r=2 then d.mobile_no
				when r=3 then d.fax_no 
				when r=4 then d.email end as abc ,
			     case 
				when r=1 then 'PHONE_NO'
				when r=2 then 'MOBILE_NO'
				when r=3 then 'FAX_NO'
			        when r=4 then 'EMAIL' end as def 
			from TABLE_B d,(select rownum r 
                                        from all_objects 
                                        where  rownum <=4 )				where d.contact_id =7011) e
              where e.abc is not null) f
where 
f.def = a.ADDRESS_EXTN_TYPE 
and a.ENTITY_TYPE='CONTACT' 
and a.address_type= 'OFFICE' 
and a.ENTITY_ID=7011)



Regards
Re: Update statement [message #219350 is a reply to message #219336] Wed, 14 February 2007 02:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
well, apparently abc can result in a null value, because one of the columns phone_no, mobile_no, fax_no or email is null.
Re: Update statement [message #219355 is a reply to message #219350] Wed, 14 February 2007 02:21 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi Frank,
Thanks for your reply.
There is no null value and even if there is one,it will be filtered out by the clause in the query:

where e.abc is not null



Regards
Re: Update statement [message #219366 is a reply to message #219355] Wed, 14 February 2007 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Would you happen to have a trigger on this table that does a Trim (or Rtrim) on the phone numbers, to remove any trailing spaces? If so, I suspect that your query returns some data thatis just spaces.
Re: Update statement [message #219369 is a reply to message #219366] Wed, 14 February 2007 03:16 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
No,there are no triggers involved here.
Even i tried Trim on the following clause (even on both sides also) but it did not worked.

f.def = Trim(a.ADDRESS_EXTN_TYPE)


Regards
Re: Update statement [message #219386 is a reply to message #219369] Wed, 14 February 2007 03:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In which case, I suspect that it's a simple problem with the update statement.

Your update statement is of the basic form:
UPDATE table
SET column = (SELECT statment)

Now, if SELECT statment fails to return a row, then the UPDATE statement will treat this as if it returned a NULL, and update Table accordingly.

There will be rows in your table TABLE_A which will not return data from your select statement.
Re: Update statement [message #219424 is a reply to message #219308] Wed, 14 February 2007 06:01 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Please elaborate about table structures.
I don't understand the need to multiplay a number of rows by 4.

Previous Topic: passing in nested (multi-dimensional) varray to procedure?
Next Topic: Dates between
Goto Forum:
  


Current Time: Wed Dec 07 22:18:51 CST 2016

Total time taken to generate the page: 0.14573 seconds