help on updating a field in a table with the field content of another table

HI everybody need help on this..

I have two tables below

table1

country countryid

africa ___
usa ___
italy ___
Spain ___

table2

countryid country name

1 africa
2 germany
3 italy
4 usa

I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..

THE RESULT WOULD BE

country countryid

africa 1
usa 4
italy 3
spain 0

thanksShow us what you've come up with so far.|||yes trying this sql and it executed well and updated the first table

UPDATE table1
SET countryid=(SELECT countryid FROM table2
WHERE cntryname=country)

thanks anyway|||the solution worked fine until it didn't encounter a duplicate value... since it is a 1 to many relationship it gave me this error...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

thanks again|||you mean you have two different countries with the same name? which ones? my geography skills aren't as good as I thought... :)|||use JOIN, something like -

UPDATE table1
SET countryid= COALESCE(table2.countryid, 0)
from table1
left outer Joint table2
ON
table2.cntryname=table1.country|||alexyeth,
I think the problem is that "africa" is not a country but continent.
:)|||thanks everybody.. the solution of mihir is great using the coalesce function...

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advanceThere is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advanceThere is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>> Hi, all
>> We have prod. database and test db(new version of prod, table fields
>> changed).
>> Production db has users connected and updating records and I need to have
>> user transactions in production db updated in test db as well.
>> I can't use log shipping to sync them because of the table structures a
>> little diff or some datatype changed in new version(test db).
>> I can't do the DTS neither. It may have too many tables effected.
>> What is the best way to do the sync.
>> thanks in advance
>>
>>
>>
>>
>>
>|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>sql

Help on sql sync

Hi, all
We have prod. database and test db(new version of prod, table fields
changed).
Production db has users connected and updating records and I need to have
user transactions in production db updated in test db as well.
I can't use log shipping to sync them because of the table structures a
little diff or some datatype changed in new version(test db).
I can't do the DTS neither. It may have too many tables effected.
What is the best way to do the sync.
thanks in advance
There is table replication in sql 2k?
Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>
|||Hi,
You can not use Logshiiping or Replication because based on my understanding
both environments are in read/write mode. Due to this we can not apply the
transaction log file. The best way for you is to write a batch file to do
frequent updates.
Thanks
Hari
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23GpcOWD2GHA.1568@.TK2MSFTNGP03.phx.gbl...
> There is table replication in sql 2k?
> Thanks
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
>
|||You can use transactional replication to replicate to tables of different
schemas. See this for more info.
http://www.dbazine.com/sql/sql-articles/cotter1
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mecn" <mecn2002@.yahoo.com> wrote in message
news:uiulJTD2GHA.476@.TK2MSFTNGP06.phx.gbl...
> Hi, all
> We have prod. database and test db(new version of prod, table fields
> changed).
> Production db has users connected and updating records and I need to have
> user transactions in production db updated in test db as well.
> I can't use log shipping to sync them because of the table structures a
> little diff or some datatype changed in new version(test db).
> I can't do the DTS neither. It may have too many tables effected.
> What is the best way to do the sync.
> thanks in advance
>
>
>
>
>