Update one table based on other table's data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update one table based on other table's data

Author

Topic

singhmao
Starting Member

4 Posts

Posted - 2009-09-14 : 09:32:16

Hi all,

I am difficult to find the answer where I have to update one table content based on other's table column value. Please see below for more clarity

I have two table CASE and CONTENT, the table Columns are

CASE :
case_id - numeric - primary key
country - varchar

CONTENT :
case_id - numeric
country - varchar
is_active - char - value could be Y or N

I want to update all the country of case table on basis of country column of content table. Here case_id of case table is primary key, and case_id of content table is forigen key. At same time only one case_id can be active (ie is_active value will be 'Y').

Please can anyone share sql statement for above condition.
I hope above statement is understable, if not then pls let me know so that I can explain it further.

Thanks
singhmao

Thanks and Regards,
Manoj Singh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 09:48:21

update t1
set t1.country=t2.country
from [CASE] as t1 inner join CONTENT as t2
on t1.case_id=t2.case_id

Madhivanan

Failing to plan is Planning to fail

singhmao
Starting Member

4 Posts

Posted - 2009-09-14 : 10:00:28

quote:
Originally posted by madhivanan

update t1
set t1.country=t2.country
from [CASE] as t1 inner join CONTENT as t2
on t1.case_id=t2.case_id

Madhivanan

Failing to plan is Planning to fail

thanks Madhivanana, for quick reply, but I am trying this in oracle and it says - sql command is not properly ended.. in line
set t1.country=t2.country

Thanks and Regards,
Manoj Singh

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-14 : 10:15:06

This is a SQL Server Forum...You may want to post the question in some Oracle Forum...but I think this should work in Oracle..

UPDATE [CASE] b
SET b.country = (SELECT a.country
FROM [CONTENT] a
WHERE a.case_id= b.case_id)

singhmao
Starting Member

4 Posts

Posted - 2009-09-14 : 10:16:46

quote:
Originally posted by vijayisonly

This is a SQL Server Forum...You may want to post the question in some Oracle Forum...but I think this should work in Oracle..

UPDATE [CASE] b
SET b.country = (SELECT a.country
FROM [CONTENT] a
WHERE a.case_id= b.case_id)

Thanks, this worked

Thanks a lot to all who replied or tried to reply

Thanks and Regards,
Manoj Singh

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-14 : 10:18:06

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources