Update an Existing table

Question

I created a table by inner joining two tables. Now i need to update the new one (My condition is if the id is not present in the new table update the table with new record ). How could i achieve this. Please help

Answer 1

I guess you need to provide more details on the problem. Can you post the innner join script you have used to create the new table? And can you elaborate on the condition part?

---

Ranjith | My Blog

Answer 2

SELECT     AccountBase.AccountId AS ID, AccountBase.Name AS [User Name], AccountBase.Telephone1 AS Telephone,
                      AccountExtensionBase.New_CustomerClass
INTO            [SMS Account]
FROM         AccountBase INNER JOIN
                      AccountExtensionBase ON AccountBase.AccountId = AccountExtensionBase.AccountId

This is the Script that i used Now i need to update the table. Account ID is same for all these tables.

Condition that i am looking For [where [dbo.SMSAccount].ID != AccountBase.AccountId]

How to write an update for this

Answer 3

My condition is if the id is not present in the new table update the table with new record

So the root cause of your problem is that joining [AccountBase] to [AccountExtensionBase] causes some records from [AccountBase] to disappear, correct?

If that is true, change your initial statement to be:

SELECT     AccountBase.AccountId AS ID, AccountBase.Name AS [User Name], AccountBase.Telephone1 AS Telephone,
                      AccountExtensionBase.New_CustomerClass
INTO            [SMS Account]
FROM         AccountBase LEFT OUTER JOIN
                      AccountExtensionBase ON AccountBase.AccountId = AccountExtensionBase.AccountId

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 4

Query is working fine for me i want to know how to update this new table. Can u give an Example for update.

Answer 5

Lets say if you are inserting or trying an update to an Account Id = 100. Try like this

 IF EXISTS (Select * from SMSAccount where ID = 100 )
 BEGIN

 END
     
     UPDATE [SMSAccount]
           SET Name = @NewName
           ,.....
        WHERE ID = @ID -- 100

 ELSE

 BEGIN

    INSERT INTO [SMSAccount] VALUES (@ID, @NewName, .......)     

 END

Not sure if this is what you are looking for.  You can put this code in a procedure or explore the MERGE statement if you are on SQL 2008

---

Ranjith | My Blog

Answer 6

Query is working fine for me i want to know how to update this new table. Can u give an Example for update.

Given that you have failed to articulate exactly what it is you want to achieve its hard to give an answer. In one sentance you say you want to update, in another you say "if the id is not present, update the table with new record". Well, that is not an update, adding new data into a table is an insertion.

Perhaps you could demonstrate by showing us some actual data in [AccountBase] & [SMS Account], before and after the operation that you want to perform.

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me