August 27, 2013 at 8:13 pm
Hi Professionals
I am running an update to update the columns and tag the licensable column as either, Licensable, Non Licensable or Unknown
problem is when there is not a match it does nothing, is there a way to also update the licensable column to unknown if there is no match
UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer,
productname = dbref.Amended_Product_name,
productversion = dbref.Amended_Product_Version,
licensable = dbref.category
FROM dbo.newtable dbsource
INNER JOIN (
SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,
Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name,category
FROM datalookuptable
GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,
Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name,category
) dbref
ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer
and dbref.Raw_Product_Version = dbsource.productversion
and dbref.Raw_Product_Name = dbsource.productname
August 27, 2013 at 11:25 pm
How about this?
UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer,
productname = dbref.Amended_Product_name,
productversion = dbref.Amended_Product_Version,
licensable = ISNULL(dbref.category,'Unknown')
FROM dbo.newtable dbsource
LEFT OUTER JOIN (
SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,
Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name,category
FROM datalookuptable
GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,
Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name,category
) dbref
ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer
and dbref.Raw_Product_Version = dbsource.productversion
and dbref.Raw_Product_Name = dbsource.productname
August 28, 2013 at 3:42 pm
No that does not work, it has updated all licensable columns to unknown and nulled the software manufacturer,productname and productversion
please advise
August 28, 2013 at 3:58 pm
the softwaremanufacturer,productname and productversion do not match in the lookuptable so it wont update with unknown,
thats what I meant to say if there is not a match in the lookuptable then if its licensable or not will be unknown
table 1
SofwareManufacturer,ProductName,ProductVersion,Licensable
test,test,test,null
test2,test2,test2,null
test3,test3,test3,null
table 2
SofwareManufacturer,ProductName,ProductVersion,Category
test,test,test,Licensable
test3,test3,test3,Non Licensable
so table 1 should change to
test,test,test,Licensable
test2,test2,test2,Unknown
test3,test3,test3,Non Licensable
I hope this makes more sense
Please advise
August 28, 2013 at 4:03 pm
If I understand this right, this MERGE statement should cut it:
MERGE dbsource
USING (SELECT DISTINCT Raw_SW_Manufacturer,Amended_SW_Manufacturer,
Raw_Product_Version,Amended_Product_Version,
Raw_Product_Name,Amended_Product_Name,category
FROM datalookuptable) AS dbref
ON dbref.Raw_SW_Manufacturer = dbsource.softwaremanufacturer
and dbref.Raw_Product_Version = dbsource.productversion
and dbref.Raw_Product_Name = dbsource.productname
WHEN MATCHED THEN
UPDATE
SET softwaremanufacturer = dbref.Amended_SW_Manufacturer,
productname = dbref.Amended_Product_name,
productversion = dbref.Amended_Product_Version,
licensable = dbref.category
WHEN NOT MATCHED BY SOURCE THEN
UPDATE
SET licensable = 'Unknown'
;
But it is all a guessing game. For this type of question it is always a good idea to post.
1) CREATE TABLE statements for your tables.
2) INSERT statement with sample data. (Enough to cover all important cases.)
3) The desired result given the sample.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 28, 2013 at 4:12 pm
Hi There
Thanks for your reply this looks more like it but I am receiving an error saying
Msg 10713, Level 15, State 1, Line 19
A MERGE statement must be terminated by a semi-colon (;).
so I put a semi colon after the final set statement and now a new error saying
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbsource'
August 29, 2013 at 2:11 am
If you want a query that has been tested, you should include
1) CREATE TABLE statements for your table.
2) INSERT statements with sample data.
3) The desired result given the sample.
You could also read about the MERGE statement in Books Online to understand what it does. But as a hint: The MERGE is followed by the name of the table to update.
By the way, the semicolon was in my original post.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply