Update a table from on schema with value from another schema

[Latyr]

Hello,

I have two schemas on my database.
One for Production and one for testing and developpment.

On that one I have update several reference data.
I want now to reflect those changes on my production.

I have tried this :

update prod.u_reagent set (CRM) =
(select CRM from test.u_reagent where test.u_reagent.u_reagentid=prod.u_reagent.u_reagentid);

I have an error saying that column name was invalid.

To isolate the problem I tried the two statements below
select CRM from test.u_reagent where test.u_reagent.u_reagentid=prod.u_reagent.u_reagentid;
select CRM from test.u_reagent where prod.u_reagent.u_reagentid=test.u_reagent.u_reagentid;

I still have an error with colomn name not valid with test.u_reagent.u_reagentid or prod.u_reagent.u_reagentid

When I issue an desc statement I can see my columns u_reagentid on both tables (prod and test).

Anyone can help me ?


Thanks in advance.

Latyr

[andrejm]

select CRM from test.u_reagent --there is missing table
where test.u_reagent.u_reagentid=prod.u_reagent.u_reagentid;


after you make
GRANT SELECT ON u_reagent TO test
GRANT SELECT ON u_reagent TO prod

you must quote both tables in query.

I did use aliases

SELECT a.crm FROM test.u_reagent b, prod.u_reagent a WHERE a.u_reagentid=b.u_reagentid;

Without aliases i dont know the solution

Andrej

[andrejm]

and update goes like this..

UPDATE prod.u_reagent a SET (crm) =
(SELECT crm FROM test.u_reagent WHERE test.u_reagent.u_reagentid=a.u_reagentid);

[Latyr]

Thanks you very much it works fine.