Update Query from table with wildcard values

[Guest]

I'm trying to create an update query that will look at the values in the
PrimaryType (table A)field and compare them to the values (7*, DCS*, etc.) in
another table (table B). If the values match, then I want to the
PimaryTypeDescription field in Table A to be updated with the corresponding
value in table B (7* --> Print, DCS* --> View). Here's the query that I have,
it does not work for the records with wildcards in the value.

UPDATE Reports
INNER JOIN ModeTable ON [reports].[PrimaryType]=[ModeTable].[BundlMode]
SET reports.PrimaryTypeDesc = [modeTable].[FormatType]
WHERE [reports].PrimaryType Like [ModeTable].[BundlMode];

any suggestions?

 

[John Spencer (MVP)]

In the join you say teh values have to be exactly alike. So things are filtered
out before you ever get to the WHERE criteria.

You could something like:

UPDATE Reports INNER JOIN ModeTable
ON [reports].[PrimaryType] LIKE [ModeTable].[BundlMode]
SET reports.PrimaryTypeDesc = [modeTable].[FormatType]

Note the change in the JOIN CLAUSE.

 

[Marshall Barton]

I'm trying to create an update query that will look at the values in the
PrimaryType (table A)field and compare them to the values (7*, DCS*, etc.) in
another table (table B). If the values match, then I want to the
PimaryTypeDescription field in Table A to be updated with the corresponding
value in table B (7* --> Print, DCS* --> View). Here's the query that I have,
it does not work for the records with wildcards in the value.

UPDATE Reports
INNER JOIN ModeTable ON [reports].[PrimaryType]=[ModeTable].[BundlMode]
SET reports.PrimaryTypeDesc = [modeTable].[FormatType]
WHERE [reports].PrimaryType Like [ModeTable].[BundlMode];

I think you have a conflict between the ON clause and the
WHERE clause.

You only JOIN records where the PrimaryType is equal to the
BundlMode, but the where clause only want them to satisfy
the Like operator.

Not sure I really understand what you're doing here (some
sample value would have helped). However, as long as there
will only be one record in ModeTable that matches each
record in Reports, I think you can drop the WHERE clause
and use a non-equi join:

UPDATE Reports
INNER JOIN ModeTable
ON [reports].[PrimaryType] LIKE [ModeTable].[BundlMode]
SET reports.PrimaryTypeDesc = [modeTable].[FormatType]

 

[Marshall Barton]

Thanks for the Help. It was a problem with the join (i used = instead of like)
My final query looks like this:

UPDATE Reports
INNER JOIN ModeTable
ON [reports].[PrimaryType] like [ModeTable].[BundlMode] & "*"
SET reports.PrimaryTypeDesc = [modeTable].[FormatType]
WHERE [reports].PrimaryType Like [ModeTable].[BundlMode] & "*";

In the ModeTable, I only have the beginning of the value that I'm looking
for (XP for all values that start with XP....), so i included a wildcard (in
the query) so the query would pickup all values with the beginning that is
listed the ModeTable.

Adding the * is fine and that will work, but you should drop
the WHERE clause. It is redundant since the ON has already
selected the matching records.