July 30, 2016 at 12:53 pm
Hi Guys,
Coming from an MS Access background I've been used to using the dlookup function which isn't available in MS SQL Server but I'm sure there are clever workarounds which I'm not aware of.
I have a table that needs to have it's CCode updated where I have a full match with TType and 'contains' the LocID in the location field.
The lookup table will be similar to the following..
CCodeTTypeLocID
=====================
6055TT 1Loc1
6054TT 2Loc2
6053TT 3Loc3
6052TT 4Loc1
6051TT 5Loc2
6050TT 6Loc3
6049TT 7Loc1
6048TT 8Loc2
6047TT 9Loc3
The table that needs to be updated with the CCode from the lookup table will have rows as follows...
CCodeTTypeLocation
==============================
TT 1text Loc1 text
TT 7text Loc1 text
TT 8text Loc2
TT 3Loc3 text
TT 2text Loc2
So the logic is as follows.... update the CCode where a match is found in TType and where LocID is contained in Location field.
The CCode in row 1 above should be updated to 6055, row 2 to 6049, row 3 to 6048 etc
I hope I've explained the problem well enough.
Thanks in advance for any pointers.
July 30, 2016 at 1:07 pm
please read this article and post back with some set up scripts and expected results
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 30, 2016 at 2:42 pm
What exactly do you mean by "contains"? Do you mean something other than "equals" Or do you mean something like
set {a, b, c} contains {b}?
How are you determining "contains" in Access? InStr()? IF that's the case, then you would need to split that out into separate records...
July 30, 2016 at 3:43 pm
I'm probably not using the correct terminology. Maybe 'like' would be a better term.
I was hoping to do something like this...
UPDATE [Table2]
SET [CCode] = (SELECT [CCode] FROM [Table1]
WHERE [TransType] = [Table2].[TransType]
AND [LocID] Like '%' + [Table2].[Location] +'%')
Is something like that possible without a join between the tables?
July 30, 2016 at 4:24 pm
Maybe something like this?
UPDATE [Table2]
SET [CCode] = Table1.[CCode]
WHERE [Table1].[TransType] = [Table2].[TransType]
AND [LocID] Like '%' + [Table2].[Location] +'%');
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply