Solved Update one field with another using like operator (1 Viewer)

[silentwolf]

Hi guys,
I rememer that I had this already found somewhere on the net but seam not be able to find it.

I like to
Update TableA , FieldA , with TableB,
When TableA FieldA is LIKE TableB, FieldB

Sure had it already found but did not need it for a while and Now I am stuck getting errors in trying

It is easy I know but am stuck and would like to finish it up ..

Thanks

 

[Pat Hartman]

This seems to be a design flaw. Perhaps you should start by breaking up columns that contain mushed values so that you have separate columns for FirstName and LastName for example rather than mushing them together.

What data are you trying to match? How will you know how to configure the wild cards?

 

[silentwolf]

Hi Pat,

thanks for your reply.

Well I have data from a CSV File the field names are long or to long. The Field is called "Umsatztext"
Because there are always multiple same names in that table I created a different table where this Text is shortend so.

125 Cr Avenue, Mr. Bill Sorrow AT1245 REF. something else.. But I like to get Mr. Bill Sorrow out of that string.



This of course can be achieved with with mid function or there like but it is not in a certain pattern for the other fields.

Therefor I have a table with one field called also Umsatztext with the appropiate names I like to have in my first Table.

I was able to do it before but got stuck guess to long in front of the pc..

So maybe I could get a help with that.

Thanks

 

[The_Doc_Man]

I am referring to the MS VBA Language Reference v20140424 for this comment. Section 5.6.9.6 describes the LIKE operator which has the special requirement that the right-hand operand must be a "like-pattern-expression". The left and right operands of LIKE are "coerced" to type STRING. If the right-hand operand is a string that includes any of ?, *, [, or # then you have a special case of pattern matching. If you DON'T have that, then the LIKE operator becomes an "=" operator after the operands on either side of the comparison have been coerced to strings.

This link below might help you with LIKE syntax.

Like Operator - Visual Basic

Learn more about: Like Operator (Visual Basic)

learn.microsoft.com

Your UPDATE syntax is off because "WITH" isn't an SQL keyword. If you want to update a record in A with data from B, you usually have to somehow JOIN tables A and B so that both tables' records are visible at the same time for the update to do the equivalent of a drag-n-drop.

Also, suppose that in one record you have Mr. Bill Morrow, in another one you have Bill Morrow (but no "Mr."), and in a third record someone got formal so you have William Morrow... were you hoping to deal with that in some way? How good is your data consistency of presentation?

 

[silentwolf]

Never mind.

Worked it out!

strSQL = "UPDATE AUSZUG, UmsatztextTable SET AUSZUG.Umsatztext = [UmsatztextTable].[Umsatztext] " & _
"WHERE (((AUSZUG.Umsatztext) Like ""*"" & [UmsatztextTable].[Umsatztext] & ""*""));"

This does the job

 

[silentwolf]

I am referring to the MS VBA Language Reference v20140424 for this comment. Section 5.6.9.6 describes the LIKE operator which has the special requirement that the right-hand operand must be a "like-pattern-expression". The left and right operands of LIKE are "coerced" to type STRING. If the right-hand operand is a string that includes any of ?, *, [, or # then you have a special case of pattern matching. If you DON'T have that, then the LIKE operator becomes an "=" operator after the operands on either side of the comparison have been coerced to strings.

This link below might help you with LIKE syntax.

Like Operator - Visual Basic

Learn more about: Like Operator (Visual Basic)

learn.microsoft.com

Your UPDATE syntax is off because "WITH" isn't an SQL keyword. If you want to update a record in A with data from B, you usually have to somehow JOIN tables A and B so that both tables' records are visible at the same time for the update to do the equivalent of a drag-n-drop.

Also, suppose that in one record you have Mr. Bill Morrow, in another one you have Bill Morrow (but no "Mr."), and in a third record someone got formal so you have William Morrow... were you hoping to deal with that in some way? How good is your data consistency of presentation?

Hi Doc,

thanks will keep that in reference !

Cheer -)
But finally was able to get it working again.. buhh

 

[theDBguy]

Never mind.

Worked it out!

strSQL = "UPDATE AUSZUG, UmsatztextTable SET AUSZUG.Umsatztext = [UmsatztextTable].[Umsatztext] " & _
"WHERE (((AUSZUG.Umsatztext) Like ""*"" & [UmsatztextTable].[Umsatztext] & ""*""));"

This does the job

But it's probably a little bit slow, because the query has to produce a cartesian product first and then filter the result down to the ones that need to be updated. I wonder if a non-equi join would be faster. Just a thought...

 

[silentwolf]

Hi there,

thanks for the input! I would need to look into that but in my case there a not really alot of data so it is for my need planty of speed.

But never know when it comes down to alot of data there is always good to know that there are different ways and perhaps better ones.

 

[Pat Hartman]

Mushing multiple attributes into a single column violates first normal form which is the most basic requirement for all relational tables. A better solution would be to fix the underlying problem and separate the attributes into individual columns.

 

[silentwolf]

Hi Pat,
yes I am aware of this but in this case it has nothing to do with relational database.
One table only updates one field with a temp file. No linked linked tables or no two field with same data in a table.