October 22, 2010 at 9:06 am
Hi, I am sure this is easy but being a newb this is confusing me. I have x2 tables that are joined by a primary key. I want to be be able to update a column in table 2 using values in table 1.
In table 1 I have a primary key column called crewmemberkey and a column called crewmemberid.
In table 2 I have the same crewmemberkey column but this time I have a column called socialsecnr.
the socialsecnr column is currently empty but I want to update this column with the same values as the crewmemberid from column1.
In simple terms. If crewmemberkey (table1) = crewmemberkey(table2) then update socialsecnr with same values as crewmemberid (table1)
I have just over 6867 rows so there is a lot of data if I have to do this row by row manually.
Does this make sense?
:unsure:
October 22, 2010 at 10:55 am
wardy (10/22/2010)
Hi, I am sure this is easy but being a newb this is confusing me. I have x2 tables that are joined by a primary key. I want to be be able to update a column in table 2 using values in table 1.In table 1 I have a primary key column called crewmemberkey and a column called crewmemberid.
In table 2 I have the same crewmemberkey column but this time I have a column called socialsecnr.
the socialsecnr column is currently empty but I want to update this column with the same values as the crewmemberid from column1.
In simple terms. If crewmemberkey (table1) = crewmemberkey(table2) then update socialsecnr with same values as crewmemberid (table1)
I have just over 6867 rows so there is a lot of data if I have to do this row by row manually.
Does this make sense?
:unsure:
BEGIN TRANSACTION;
UPDATE T2
SET socialsecnr = T1.crewmemberid
FROM Table2 T2
JOIN ON Table1 T1
ON T1.crewmemberkey = T2.crewmemberkey
WHERE T2.socialsecnr IS NULL;
ROLLBACK TRANSACTION;
After you're sure that this works properly, run it without the transaction (or change ROLLBACK to COMMIT).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 22, 2010 at 10:56 am
OK So, to break it down step by step to hopefully allow better understanding...
--do this someplace safe
USE SSCTest;
--Create my test tables
CREATE TABLE table1
(
crewmemberkey INT,
crewmemberid INT
);
CREATE TABLE table2
(
crewmemberkey INT,
socialsecnr INT
);
--Populate with data
INSERT INTO [table1]
SELECT 123, 111111111 UNION ALL
SELECT 222, 222222222 UNION ALL
SELECT 333, 333333333;
INSERT INTO [table2] ( [crewmemberkey])
SELECT 123 UNION ALL
SELECT 222 UNION ALL
SELECT 333 ;
--Make sure it's where I think it is'
SELECT [table1].[crewmemberkey],
[table1].[crewmemberid],
[table2].[crewmemberkey],
[table2].[socialsecnr]
FROM [table1]
INNER JOIN [table2]
ON [table1].[crewmemberkey] = [table2].[crewmemberkey];
--do the update
--You need to use an alias to the tables which you've join in the from line.
UPDATE t2
SET t2.[socialsecnr] = t1.[crewmemberid]
FROM [table1] t1
INNER JOIN [table2] t2
ON t1.[crewmemberkey] = t2.[crewmemberkey];
--Verify the update
SELECT [table1].[crewmemberkey],
[table1].[crewmemberid],
[table2].[crewmemberkey],
[table2].[socialsecnr]
FROM [table1]
INNER JOIN [table2]
ON [table1].[crewmemberkey] = [table2].[crewmemberkey];
--cleanup
DROP TABLE table1;
DROP TABLE table2;
-Luke.
October 22, 2010 at 10:57 am
October 22, 2010 at 11:01 am
Cheers guys, I have copied the database over to a test database so I will try on this one first.
I was looking at merge statements and was getting confused, this looks much simpler.
October 26, 2010 at 7:50 am
Hi, I have amedned the sript to use the valid table names and parsed the quesry toc heck before running but received the usual SQL error message as per below.
BEGIN TRANSACTION;
UPDATE T2
SET socialsecnr = T1.crewmemberid
FROM sbscrewmember T2
JOIN ON sbsairlinecrewmember T1
ON T1.crewmemberkey = T2.crewmemberkey
WHERE T2.socialsecnr IS NULL;
ROLLBACK TRANSACTION;
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ON'.
I have looked at a few other references and cannot see why this would be failing. Any advise gratefully received.
October 26, 2010 at 7:55 am
October 26, 2010 at 8:01 am
That will teach me to just copy and paste! Should have checked before I posted. dohh
Anyhow I have corrected and althogh it now runs there are 0 rows affected and nothing appears to have changed. Oh well back to the drawing board.
October 26, 2010 at 8:20 am
wardy (10/22/2010)
Hi, I am sure this is easy but being a newb this is confusing me. I have x2 tables that are joined by a primary key. I want to be be able to update a column in table 2 using values in table 1.In table 1 I have a primary key column called crewmemberkey and a column called crewmemberid.
In table 2 I have the same crewmemberkey column but this time I have a column called socialsecnr.
the socialsecnr column is currently empty but I want to update this column with the same values as the crewmemberid from column1.
In simple terms. If crewmemberkey (table1) = crewmemberkey(table2) then update socialsecnr with same values as crewmemberid (table1)
I have just over 6867 rows so there is a lot of data if I have to do this row by row manually.
Does this make sense?
:unsure:
Wayne and Luke have shown you how to construct an UPDATE...FROM. Here's a question for you - how will you be able to tell, after the update, if the socialsecnr column in table 2 contains a social security number or a crew member id?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 9:16 am
At the moment the socialsecnr column is empty as it is not used, however going forward I need to use this column as part of an import process that uses this column as a unique key.
I was imply going to run a select command to see if the crewmemberid and socialsecnr columns matched.
However at the moment the script is not working.
October 27, 2010 at 2:15 am
Thank you to all contributors on this one. After removing the extra ON statement and changing the IS NULL to ='' the update worked perfectly.
😀
October 27, 2010 at 6:17 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply