Update SQL tables
-
Hi All,
I am very new to SQL programing and I am trying to update a table by receiving the data from another table which has the same fields as the other one.
Table1 and table2 each one has 2 fields server name and server owner
Table1 is out to date and table2 is up to date and I need to update table1's server owner field for all rows from table2 which is up to date.
The Server name fields are not in order so I need a statement to match the server names and update table2 from table1 and that will just update the server owner field from table2 to table1
-
Anything forbidding you to drop the table's data and insert all the data from the 2nd server?
-
Yes
The table I want to update has additional fields and I don't want to loose them.
I also need to add a statement that the fields in both tables match at least 3 characters cause in one of the tables the server names are entered as server.domain.net and on the other table are entered as just the server name server
-
Please post some sample data from both tables and the required outout of the updated table. The table ddl is also quite usefull to us.
-
As best as I can tell, you'll need to do this in 3 passes. Each is a fairly simple INSERT (new rows in table 2), UPDATE (existing rows that are both valid), and DELETE (records no longer present in table 2). Each one statement will be fairly similar, but the JOIN clauses to link the 2 tables will be different. If you provide the basic schemas, I'm sure someone here will throw it together for you...
-
update table1
set [server owner] = table2.[server owner]
from table2
where table1.[server name] = table2.[server name]
(or something like that)
-
Thamks It was very helpful
-
Heh... if you posted some data, it wouldn't have been helpful... it would have been done.
I know you're a newbie here but ya gotta trust me on this... if you post CREATE TABLE for the columns concerned and some data in the form of INSERT/SELECT/UNION ALL, you'll be amazed at the great answers you get.
Personally, I tend to do responses for folks who take the time to post the necessary DDL and data, first. I sometimes never get to posts that don't have those essentials. Why? Because, like many of us on these forums, we want to test our answers before we post them and creating the DDL and some reasonable test data usually takes more time that writing the answer. You, on the other hand, have access to both.
Help us help you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is the basic Syntax:
update table1
SET serverowner = (
select serverOwner
from table2
where table1.servername = table2.servername
)
where servername in (select servername from table2)
GO
Sorry for the late reply, I overlooked some older emails subscriptions.
----------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply