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

As you don't have a WHERE clause on your UPDATE, you ARE updating the 1 million rows in BIG_TABLE.

You need something like (I haven't even tried to optimise this):

UPDATE BIG_TABLE B
 SET B.MY_COLUMN =
 (SELECT S.MY_COLUMN FROM SMALL_TABLE S WHERE B.ID_COLUMN = S.ID_COLUM)
WHERE B.ID_COLUMN IN (SELECT ID_COLUMN from SMALL_TABLE)

But, your other problem appears to be that MY_COLUMN in small_table has NULL values, and MY_COLUMN in BIG_TABLE does not allow null values.

Check your data and the table definitions before you continue.

Cheers!

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!!
Received on Tue May 07 2002 - 04:22:44 CEST