SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stuck on Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle765
Starting Member

Australia
13 Posts

Posted - 03/06/2013 :  20:55:56  Show Profile  Reply with Quote
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;
GO

UPDATE dbsource SET software_name_raw = dbref.software_name_amended
FROM dbo.BigTable dbsource
INNER 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_raw
go
COMMIT 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 this

ELSE INSERT INTO RefTable(software_name_raw,software_name_amended)
Values BigTable(software_name_raw,’Needs Updating’)

How can this be amended.

Thanks

A Lynch

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/06/2013 :  23:25:53  Show Profile  Reply with Quote

BEGIN TRANSACTION Inner1;

UPDATE dbsource SET software_name_raw = dbref.software_name_amended 
FROM dbo.BigTable dbsource
INNER 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_raw

INSERT RefTable(software_name_raw,software_name_amended)
SELECT software_name_raw,'Needs Updating'
FROM BigTable b
WHERE NOT EXISTS (SELECT 1 FROM RefTable WHERE software_name_raw = b.software_name_raw)
COMMIT TRANSACTION Inner1;


Another way is to use MERGE statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000