Updating table [message #38379] |
Tue, 16 April 2002 02:46 |
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 |
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 |
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 |
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 |
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 |
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."
|
|
|
|
|