Updating two tables

Rated 4.23/5 based on 881 customer reviews

After the tables have been created and the data added, changing or updating data in the tables becomes one of the day-to-day processes in maintaining a database.SQL Server provides the following ways to change data in an existing table.But a couple columns in the table, will be changing. But I will use MERGE once I get this other figured out Thank You Greatly Andrew SQLDBA UPDATE ODSET OD. If the values have changed, I need to update the data in the other table.--===== If the test table already exists, drop it IF OBJECT_ID('Temp DB..#Original Data','U') IS NOT NULL DROP TABLE #Original Data--===== Create the test table with CREATE TABLE #Original Data ( Row ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , Member ID int , Location ID varchar(5) , First Name varchar(25) , Last Name varchar(25) , Birthdate date , Area Code int , Phone Number varchar(8) ) --===== If the test table already exists, drop it IF OBJECT_ID('Temp DB..#Updated Data','U') IS NOT NULL DROP TABLE #Updated Data--===== Create the test table with CREATE TABLE #Updated Data ( Row ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , Member ID int , Location ID varchar(5) , First Name varchar(25) , Last Name varchar(25) , Birthdate date , Area Code int , Phone Number varchar(8) )INSERT INTO #Original Data (Member ID, Location ID, First Name, Last Name, Birthdate, Area Code, Phone Number) SELECT 222, 'Viol','Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL SELECT 223, 'Green','Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL SELECT 224, 'Green','Michelle','Foster','1966-12-12', 657,'241-7815' UNION ALL SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL SELECT 226, 'Red', 'Joe','Roberts','1960-05-05', 211,'205-8785' UNION ALL SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL SELECT 228, 'Blue', 'Andy','Hill','1966-08-01', 315,'473-2501' UNION ALL SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670' INSERT INTO #Updated Data (Member ID, Location ID, First Name, Last Name, Birthdate, Area Code, Phone Number) SELECT 222, 'Viol', 'Sammy','Miller','1965-12-15', 888, '555-1212' UNION ALL SELECT 223, 'Green', 'Sarah','Smith','1968-03-02', 523,'458-2356' UNION ALL SELECT 224, 'Green', 'Michelle','Foster','1966-12-12', 657,'241-7800' UNION ALL -- Changed Phone Number SELECT 225, 'Gray', 'Robert','Gillum','1970-07-20', 879,'251-6300' UNION ALL SELECT 226, 'Red', 'Joe','Roberts','1960-05-07', 211,'205-8585' UNION ALL -- Changed Birthdate, Phone Number SELECT 227, 'Red', 'Danny','Jones','1961-10-30', 696,'575-5611' UNION ALL SELECT 228, 'Blue', 'Andrew','Hill','1966-08-01', 315,'473-2502' UNION ALL -- Changed First Name, Phone Number SELECT 229, 'Blue', 'Bill','Height','1962-11-20', 315,'474-5670' SELECT * FROM #Original Data SELECT * FROM #Updated Data DROP TABLE #Original Data DROP TABLE #Updated Data Member ID and Location ID are the only two columns that can never change. Phone Number FROM #Original Data AS ODINNER JOIN #Updated Data AS UDON OD. -- Red Adair MERGE #Original Data original USING #Updated Data changed ON original.

Along with 16 years of hands on experience he holds a Masters of Science degree and a number of database certifications.

| Lee | | 1012 | Annie | NULL | Wats | | 1013 | Alan | NULL | Wang | | 1014 | Nelson | NULL | Yin | -------- ----------------- ------------------ ---------------- 9 rows in set (0.01 sec) mysql select * from Authors; -------- ----------------- ------------------ ---------------- | Auth ID | Author First Name | Author Middle Name | Author Last Name | -------- ----------------- ------------------ ---------------- | 1006 | Henry | S.

This is one of the most interesting questions I keep on getting on this email and I find that not everyone knows about it. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. Can you please let me know what are the cases multipart identifier error message will be shown.

If any row in the #Updated Data table, except for Member ID or Location ID is different that Update #Original Data row with the same row in the #Updated Data table. But if I just query the two tables, I am getting a huge number of rows back, when I know there are only two rows that are different between the two tables.

As you can see, I have set 3 rows in the #Updated Data table to be different than the rows in the #Original Data table.

Leave a Reply