update - SQL Server Forums

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

Author

Topic

edo
Starting Member

15 Posts

Posted - 2009-03-12 : 09:43:07

Hello,

I try to update my table test1 from database DB1 with values from table test2 from database DB2.
I work in SQL Server 2005.
Can anyone help me?

UPDATE [DB1].[dbo].[table1]
SET table1.col1 = table2.col1
SET table1.col2 = table2.col2
SET table1.col3 = table2.col3
SET table1.col4 = table2.col4
FROM [DB2].[dbo].[table2] WHERE table2.oldCol = table1.col

heavymind
Posting Yak Master

115 Posts

Posted - 2009-03-12 : 09:58:55

UPDATE tab1
SET tab1.col1 = tab2.col1
SET tab1.col2 = tab2.col2
SET tab1.col3 = tab2.col3
SET tab1.col4 = tab2.col4
FROM [DB1].[dbo].[table1] as tab1
inner join [DB2].[dbo].[table2] as tab2 on tab2.oldCol = tab1.col

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-13 : 01:58:43

UPDATE tab1
SET tab1.col1 = tab2.col1,
tab1.col2 = tab2.col2,
tab1.col3 = tab2.col3,
tab1.col4 = tab2.col4
FROM [DB1].[dbo].[table1] as tab1
inner join [DB2].[dbo].[table2] as tab2 on tab2.oldCol = tab1.col

edo
Starting Member

15 Posts

Posted - 2009-03-13 : 06:30:36

I w'd like to know is col1 abbreviation for my column name or a sql command? what is now my oldcol and why the last col has no numerical name?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 06:51:20

col1 and oldcol are the names of columns. You can have an alphanumeric name of column/table/indexes/views etc.

edo
Starting Member

15 Posts

Posted - 2009-03-13 : 07:51:30

Ok. But my tables and columns have the samme name! Tables names are adress and columns names are street, town.

UPDATE [DB1].[dbo].adress
SET adress.street = adress.street
FROM [DB1].[dbo].[adress] as adress
inner join [DB2].[dbo].[adress] as adress on adress.street = adress.street

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 07:55:50

Its difficult to guess that from your first post. Anyhow, This should work ok.

UPDATE  adress1
SET adress1.street = adress2.street
FROM [DB1].[dbo].[adress] as adress1
inner join [DB2].[dbo].[adress] as adress2 on adress1.street = adress2.street

edo
Starting Member

15 Posts

Posted - 2009-03-13 : 08:48:37

Thank's for help but I can not get it to work.

Should my adress tables have that number 1 and 2 or is it enough with just adress?
Ex. UPDATE adress
SET adress.street = adress.street ...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 08:53:35

Thats fine. You needn't change the name of the table. Can you post us what the problem is?

edo
Starting Member

15 Posts

Posted - 2009-03-13 : 09:49:14

I get this error message:

SQL Execution Error. Executed SQL statement:UPDATE adress1
SET adress1.mslink = adress2.mslink
FROM DB1.dbo.adresse AS adress1 INNER JOIN
DB2.dbo.adresse AS adress2 ON adress1.mslink = adress2.mslink CROSS JOIN adress1
Error Source: .Net SqlClient Data Provider
Error Message: Invalid object name 'adress1'.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 10:06:36

[code]UPDATE adress1
SET adress1.mslink = adress2.mslink
FROM DB1.dbo.adresse AS adress1 INNER JOIN
DB2.dbo.adresse AS adress2 ON adress1.mslink = adress2.mslink CROSS JOIN DB1.dbo.adresse[/code]

edo
Starting Member

15 Posts

Posted - 2009-03-16 : 10:49:42

I can use that SQL and my query executed successfully, but I get message "(0 (rows) affected). I have data in table adresse2 and my table adresse1 that I want to update is still empty. What is a problem now?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 10:55:40

how will you update adresse1 if its empty? then what you need is insert rather than update


INSERT INTO [DB2].[dbo].adress
SELECT columns...
FROM [DB1].[dbo].adress

edo
Starting Member

15 Posts

Posted - 2009-03-17 : 08:10:22

This one takes all data from a table and inserts it to the other table. In that way I get a lot of duplicate data, next time I execute SQL. So, I think I need a SQL UPDATE to update my table with the newest data.
I have table adress with 3 rows data in DB1 and I want to update that table with the newest data from another database DB2's adress table.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 08:22:42

I had just corrected your query where you were using cross join. You will get duplicates using cross join there. Tell us what exactly you need. You didn't use the query I had provided earlier but instead added cross join there. Tell us what you ran, what problem you see and post sample data and output.

edo
Starting Member

15 Posts

Posted - 2009-03-17 : 10:08:12

It's very simpel. I have 3 data rows in address table that lie in database DB1. And I have 7 data rows in another adress table that lie in database DB2. I will bring up to date my first table with data from the other table. If I work UPDATE sql with INNER JOIN I get this message 3(rows) affected. But nothing happens in my tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:25:36

whats your common field in both the tables? may be identity or pk column?

edo
Starting Member

15 Posts

Posted - 2009-03-17 : 10:49:14

Booth tables have 5 columns. Both tables columns have the same name. There 3 rows data in 3 columns in my first table and 7 rows data in 3 columns in my second table.
3 rows data are identity in both tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 10:52:35

then what you need is this


INSERT INTO DB2.dbo.Table
SELECT t1.columns...
FROM DB1.dbo.Table t1
LEFT JOIN DB2.dbo.Table t2
On t1.PK=t2.PK
WHERE t2.PK IS NULL

edo
Starting Member

15 Posts

Posted - 2009-03-18 : 05:09:29

What is PK?

INSERT INTO DB2.dbo.adress
SELECT adress.street, streetnumber, town, postnumber, telephon
FROM DB1.dbo.adress
LEFT JOIN DB2.dbo.adress
On adress.PK=adress.PK
WHERE adress.PK IS NULL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-18 : 05:12:26

pk means primary key column in ur adress table

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources