Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
oracle765
Starting Member
13 Posts |
Posted - 2013-03-06 : 20:55:56
|
Hi Professionals I am running the following query as advised previously which updates the source table based on a column from the reference table matching...BEGIN TRANSACTION Inner1;GOUPDATE dbsource SET software_name_raw = dbref.software_name_amended FROM dbo.BigTable dbsourceINNER JOIN ( SELECT software_name_raw,software_name_amended FROM RefTable GROUP BY software_name_raw,software_name_amended) dbref ON dbref.software_name_raw = dbsource.software_name_rawgoCOMMIT TRANSACTION Inner1;I have run into a problem which is. If they dont match I need to update the reference tables 2 columns with the new unmatched record to reference something like thisELSE INSERT INTO RefTable(software_name_raw,software_name_amended)Values BigTable(software_name_raw,’Needs Updating’)How can this be amended.ThanksA Lynch |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 23:25:53
|
[code]BEGIN TRANSACTION Inner1;UPDATE dbsource SET software_name_raw = dbref.software_name_amended FROM dbo.BigTable dbsourceINNER JOIN (SELECT software_name_raw,software_name_amended FROM RefTableGROUP BY software_name_raw,software_name_amended) dbref ON dbref.software_name_raw = dbsource.software_name_rawINSERT RefTable(software_name_raw,software_name_amended)SELECT software_name_raw,'Needs Updating'FROM BigTable bWHERE NOT EXISTS (SELECT 1 FROM RefTable WHERE software_name_raw = b.software_name_raw)COMMIT TRANSACTION Inner1;[/code]Another way is to use MERGE statement------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|