How do i compare & copy a name from one DB to another?
-
I am trying to copy item names from one database to another if they share the same code number.
e.g.
UPDATE TABLE1
SET NAME = DB2.TABLE2.NAME
WHERE CODE = DB2.TABLE2.CODE
I dont know how to specify the second db as using a '.' here doesnt seem to work. I have also tried '..' and DB2.dbo.TABLE2 but get the same error - 'The column prefix 'Train_DB_FinancialsProd.dbo.ITEM_DEFINITIONS' does not match with a table name or alias name used in the query.'
I have checked that the spelling is ok so not sure why it doesnt work.
I have also tried using a temp db in between to copy the values across then try to update from the temp table but couldnt make that work either. e.g.
create table #tdhname
(
code char(50),
name char(100)
)
INSERT INTO #tdhname (code,name)
select distinct code,name from Train_DB_FinancialsProd..item_definitions tritm
where code = tritm.code
and tritm.ittyp_refno = 241
update pharmacy_items
set name = #tdhname.name <----- THIS DOESNT WORK
where #tdhname.code = code
Thanks!
-
So it looks like you're trying to do this?
Update Table1
Set T1.[Name] = T2.[Name]
From Table1 as T1
Inner Join DB2.Table2 as T2 On T1.[Name] = T2.[Name]
It may not be exactly right (ie you might not have name as what you want to join on but i'm sure it'll give you somewhere to start from)
-
Thanks!
using the inner join worked. Im just learning sql so dont know too much on joins. Im getting there though!
Cheers
-
No problem whatsoever, i've only been doing this for 8 years so i'm still just learning myself
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply