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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stuck on Update

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;
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

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 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;
[/code]

Another way is to use MERGE statement

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

Go to Top of Page
   

- Advertisement -