sql update two databases on same server

[mdacoron]

Hey guys,

I am in no way any type of database admin but I have this dilemma with a sql server that I am trying to update some table information on.

We are running this imaging system that uses microsoft sql 2005. There are two separate databases on the server and I want to update information on one of the database tables from another database table.

In the object explorer tab when I open the databases folder I have 5 things listed. I only use two of them. One of them is called AX and another called Kfx. I know how to do searches for a specific column with certain data needed.

If I go into one of the databases then go into one of the tables, I can run a sql query for information in that table. Usually what I do to do this is to go into that table by right clicking and selecting open table. After doing that I can go and click on show sql pane and run a query on any column in that table fine. That is the extend to my knowledge.

My question now is how do I run a sql update to update information from one database table to another database's table?

 

[jbenson001]

if that is the case then you just need a WHERE clause:

Update ae_dt1
Set <your col> = <some value>,
    <your col2> = <some value2>  --, ... more cols here
From ae_dt1 a
   Inner Join
      Kfx.<table owner>.HarvestImport h ON
      a.InvoiceNumber = h.InvoiceNumber 
[blue]WHERE h.total = 0[/blue]

 

[mdacoron]

I think that worked thanks!!!

 

[jbenson001]

ok .. good luck

 

[mdacoron]

i did notice after running it that the date column added
<< and >> on the field so it started like 08/08/2008 and then changed it to <<08/08/2008>>.
Any reason why? its good its only like 800 records so i can fix that.

 

[jbenson001]

It's probalby how you typed in the code I gave. You aren't supposed to actually type in the <> that was just to show you that is a place holder for a value. What is the exact code you typed in?

 

[mdacoron]

Update ae_dt1
Set field1 = h.Invoice,
field2 = h.[Order #],
field3 = h.[Cust #],
field4 = h.[Cust Name],
field5 = h.[Ship To #],
field6 = h.[Ship To Name],
field7 = h.[Date],
field8 = h.[Total]
From ae_dt1 a
Inner Join
Kfx.dbo.HarvestImport h ON
a.field1 = h.Invoice
where
h.Total = 0.0

 

[jbenson001]

If this is the code you ran then, the only way the column data is in that format is because that is what is in the HarvestImport table.