update set problem [message #10709] |
Wed, 11 February 2004 10:54 |
denise
Messages: 64 Registered: November 2000
|
Member |
|
|
I'm trying to update a table but the command doesn't work. Does anyone see anything wrong with this:
update state_lics_mstr upin_temp
set state_lics_mstr.upin = upin_temp.upin
where state_lics_mstr.license_nbr = upin_temp.license_nbr
The error I get is that state_lics_mstr.license_nbr is an invalid column name. It definitely is valid. Thanks for any help with this.
|
|
|
Re: update set problem [message #10710 is a reply to message #10709] |
Wed, 11 February 2004 11:24 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
If there is a matching row in upin_temp for each license_nbr value in state_lics_mstr, or you don't mind setting non-matching rows to null, then:
update state_lics_mstr slm
set upin = (select ut.upin
from upin_temp ut
where ut.license_nbr = slm.license_nbr);
If only some rows in state_lics_mstr have a match in upin_temp, and we don't want to update non-matching rows, then:
update state_lics_mstr slm
set upin = (select ut.upin
from upin_temp ut
where ut.license_nbr = slm.license_nbr)
where exists (select null
from upin_temp ut
where ut.license_nbr = slm.license_nbr);
|
|
|
Re: update set problem [message #10711 is a reply to message #10709] |
Wed, 11 February 2004 11:31 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
You gave an alias to a table you update and then you want Oracle to go to an original name...
And what are you trying to do? Update table with upin from the same table for the same license_nbr?
I guess you missed something.
|
|
|
Re: update set problem [message #10712 is a reply to message #10711] |
Wed, 11 February 2004 13:05 |
denise
Messages: 64 Registered: November 2000
|
Member |
|
|
I'm trying to update state_lics_mstr field upin from another table's (upin_temp)field also called upin.
I'm not trying to give state_lics_mstr an alias, I though I had to list both tables that I'm referencing in the first line. I tried this with a comma between the two table names like:
update state_lics_mstr, upin_temp
set state_lics_mstr.upin = upin_temp.upin
where state_lics_mstr.license_nbr = upin_temp.license_nbr
But then had error "missing SET keyword"
Thanks for trying to help.
|
|
|
Re: update set problem [message #10713 is a reply to message #10712] |
Wed, 11 February 2004 13:47 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
The syntax for updating an inline view would be:
UPDATE ( SELECT slm.upin old_upin
, ut.upin new_upin
FROM state_lics_mstr slm
, upin_temp ut
WHERE ut.license_nbr = slm.license_nbr )
SET old_upin = new_upin;
However this will only work where appropriate foreign and unique keys exist linking the two tables, so that the database can be certain that each row being updated corresponds to exactly one row in the base table. I'm guessing that there aren't keys linking STATE_LICS_MSTR and UPIN_TEMP.
|
|
|
Re: update set problem [message #10715 is a reply to message #10713] |
Wed, 11 February 2004 14:35 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
The lack of appropriate keys is why I hardly ever use this approach in a reply because it almost always opens up a can of worms re: ORA-01779 errors.
|
|
|
|