Re: How to do an UPDATE with data from another table?

lgm_at_tid.es (Laura I+D) wrote in message news:<f6db481b.0205061026.42761bec_at_posting.google.com>...
> Hi
>
> I have two identical tables, one of them with one million register.
> The other with 10.
>
> Those 10 registers exists in the big table, but they have a more
> actualizaed value in one of the columns in the small table. I want to
> do an UPDATE on the big table to update these 10 registers with the
> value that they have in the small table.
>
> UPDATE BIG_TABLE B
> SET B.MY_COLUMN =
> (SELECT S.MY_COLUMN FROM SMALL_TABLE S WHERE B.ID_COLUMN = S.ID_COLUM)
>
> The problem is that it always return:
>
> ORA-01407: cannot update ("CRMS"."BIG_TABLE"."MY_COLUMN") to NULL.
>
> It seems like if it was trying to update the one million registers and
> returns error because it doesn't find all the rows in the small table!
> How can I restrict the sentece to update just 10 rows and not one
> million?
>
> Thank you!!

UPDATE BIG_TABLE B
SET B.MY_COLUMN =
(SELECT S.MY_COLUMN FROM SMALL_TABLE S WHERE B.ID_COLUMN = S.ID_COLUM) where exists
(select 'x'
 from small_table s
 where s.id_column = b.id_column)

--
Hth

Sybrand Bakker
Senior Oracle DBA