CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Dante Snow
Dante Snow

Reputation: 77

How to update a table from 2 others that contain the old values and the new values in SQL Server?

I have 3 tables, 2 of them are temporary tables. One has old values to be updated and the other has the new values.

The first table contains the columns Telephone and ID, and the other two only contain one column, telephones

Example

Table TelephonesOrganization :

ID        Telephone 
1          1234
1          4567
1          5678

Table TelephonesToUpdate:

Telephone
1234
4567

Table TelephonesNewValues:

9999
9999

The values to update in TelephonesToUpdate correspond directly to the same row in TelephonesNewValues, for example 1234 will take the value 9999, the ID is also a variable In this case Its 1.

I have tried using and UPDATE and in the Where compare the Telephones and the id but for some reason I get duplicated entries. 

Declare @ID int 
Set @ID = 1
UPDATE A
SET A.Telephone = 12345  FROM TelephoneOrg A, @TempOLDTelephones B , @TempNEWTelephones C
WHERE A.Telephone = B.Telephone and A.ID = @ID

Thanks in advance for any answer,

Dante.

Upvotes: 0

Views: 60

Answers (2)

Mike Harris
Mike Harris

Reputation: 869

I believe you have no id between TelephonesToUpdate and TelephonesNewValues but the order is the same, so we can create one with ROW_NUMBER: http://msdn.microsoft.com/en-us/library/ms186734(v=sql.100).aspx

UPDATE t
  SET t.Telephone = b.Telephone
FROM TelephonesOrganization AS t
INNER JOIN
(
  SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'ID'
   ,Telephone
   FROM TelephonesToUpdate
) AS a
  ON t.Telephone = a.Telephone
INNER JOIN
(
  SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'ID'
   ,Telephone
   FROM TelephonesNewValues
) AS b
  ON a.ID = b.ID
;

Here is a SQL Fiddle you can play with: http://sqlfiddle.com/#!3/3c15d/3

Upvotes: 1

Michael O'Neill
Michael O'Neill

Reputation: 954

update TelephonesOrganization o
   set o.Telephone = ( select u.TelephonesNewValues
                         from TelephonesToUpdate u
                        where u.Telephone = o.Telephone )
 where o.ID = 1
   and exists ( select u.TelephonesNewValues
                  from TelephonesToUpdate u
                 where u.Telephone = o.Telephone );

UPDATE: I answered this before the question was updated. I did not think TelephoneNewValues was a table. I thought it was a column of the TelephoneToUpdate table. Also I had an error in my exists clause.

Upvotes: 0

Related Questions