Home » SQL & PL/SQL » SQL & PL/SQL » update set problem
update set problem [message #10709] Wed, 11 February 2004 10:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: update set problem [message #10737 is a reply to message #10710] Fri, 13 February 2004 06:12 Go to previous message
denise
Messages: 64
Registered: November 2000
Member
Todd:
The second model did exactly what we needed it to do. Thanks a lot for the help,
Denise
Previous Topic: Function index
Next Topic: Syntax for creating views
Goto Forum:
  


Current Time: Thu Mar 28 07:03:14 CDT 2024