Home » SQL & PL/SQL » SQL & PL/SQL » Updating table
Updating table [message #38379] Tue, 16 April 2002 02:46 Go to next message
Glenn
Messages: 10
Registered: November 2001
Junior Member
Help!

When updating a table using an update (select) my script updates the matching records and nulls all other records for that field.

A simple example of my script is

UPDATE TEST1
SET (B) = (SELECT D FROM TEST2
WHERE C=A )

The table I want to update starts like this
"test1"


- A - - B -


- DUBLIN - - 10 -


- PARIS - - 20 -

I select from this
"test2"


- C - - D -


- DUBLIN - - 88 -


- LONDON - - 99 -

and test1 ends up like this


- A - - B -


- DUBLIN - - 88 -


- PARIS - -   -

Dublin is updated correctly but Paris is null. It should have kept the value 20.

Thanks Glenn
Re: Updating table [message #38380 is a reply to message #38379] Tue, 16 April 2002 04:01 Go to previous messageGo to next message
Epe
Messages: 99
Registered: March 2002
Member
Hello,

in that case you should specify a where clause that tells that only those records should be updated for which a value exists in test2 :
i.e.:
UPDATE TEST1
SET (B) = (SELECT D FROM TEST2
WHERE C=A )
where A in (select distinct C from test2);

Success,

epe
Re: Updating table [message #38382 is a reply to message #38379] Tue, 16 April 2002 04:26 Go to previous messageGo to next message
Glenn
Messages: 10
Registered: November 2001
Junior Member
Thanks epe

Although this will work its more af a work around solution.

The problem is that the join c=a should also work for the update.
Re: Updating table [message #38384 is a reply to message #38379] Tue, 16 April 2002 06:04 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
Glenn, When you say "should also work for the update", can you point to documentation or is that your opinion of how it should work? I think it is quite logical: When I say "UPDATE TEST1 SET B = " without a WHERE clause, SQL should logically presume that I want every COL B in TEST1 to be SET. The purpose of a WHERE clause in an UPDATE statement is to limit those rows that get set; in this case, only those rows where there is a matching value in TEST2.

Now, as to why it sets it to null, that too is quite logical. If you buy into my argument above (and you probably don't) that it should update every row since there is no WHERE clause on the UPDATE statement, then if it doesn't find a row in TEST2 where A=C, a NULL value seems appropriate. At least, IMHO.
Re: Updating table [message #38391 is a reply to message #38379] Tue, 16 April 2002 07:48 Go to previous messageGo to next message
Glenn
Messages: 10
Registered: November 2001
Junior Member
Jon

I say 'should' because of this example and my experience in SQL server. The where clause explanation give the precise reason.

SET (column_name[[, column_name]]...) =
This clause assigns the values retrieved from the database by subquery to the columns in the column_name list. The subquery must return exactly one row that includes all the columns listed.

The column values returned by subquery are assigned to the columns in the column_name list in order. Thus, the first value is assigned to the first column in the column_name list, the second value is assigned to the second column in the column_name list, and so on.

In the following correlated query, the column item_id is assigned the value stored in item_num, and the column price is assigned the value stored in item_price:

UPDATE inventory inv -- alias
SET (item_id, price) = (SELECT item_num, item_price FROM item_table
WHERE item_name = inv.item_name);

WHERE search_condition
This clause chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit the search condition, all rows in the table are updated.

This example can be found @

http://www-rohan.sdsu.edu/doc/oracle/server803/A54654_01/13_elem3.htm#2506
(you need to scroll near to end)
Re: Updating table - you were misreading the doc [message #38396 is a reply to message #38379] Tue, 16 April 2002 09:34 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
I can see why you were confused. The example is misleading, no doubt why it was removed from subsequent versions. The "WHERE search_condition" that you cite is the WHERE clause for the UPDATE statement, not for the SQL_expression of the SET statement (see the diagram above). It states clearly that if you omit the search condition, all rows in the table are updated. This is true. Following your link, I was also able to confirm that the column will be updated with NULL when the subquery does not return a value.

I followed your link below...

http://www-rohan.sdsu.edu/doc/oracle/server803/A54654_01/13_elem3.htm#2506

"For a full description of the UPDATE statement, see Oracle8 Server SQL Reference."

[[Following that link, I found]]

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/ch4m.htm#5853

"The WHERE clause determines the rows in which values are updated. If the WHERE clause is not specified, all rows are updated. For each row that satisfies the WHERE clause, the columns to the left of the equals (=) operator in the SET clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated."

and

"If the SET clause contains a subquery, it must return exactly one row for each row updated. Each value in the subquery result is assigned respectively to the columns in the parenthesized list. If the subquery returns no rows, then the column is assigned a null. Subqueries may select from the table being updated."
Re: Updating table - you were misreading the doc [message #38408 is a reply to message #38379] Wed, 17 April 2002 00:10 Go to previous messageGo to next message
Glenn
Messages: 10
Registered: November 2001
Junior Member
Jon

I followed your link and am convinced now.

Thanks for your time.

Glenn, Dublin, Ireland

Where r u?
Re: Updating table - you were misreading the doc [message #38412 is a reply to message #38379] Wed, 17 April 2002 03:17 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
Hartford, CT, USA
Previous Topic: explanation needed
Next Topic: question
Goto Forum:
  


Current Time: Thu Mar 28 07:42:44 CDT 2024