Update Query

N

Nuno Guerra

Hi!

I have this SQL statment that doesn't work:
UPDATE TAB3 INNER JOIN TAB4 ON TAB3.User = TAB4.USER_ID SET TAB3.[User] =
[TAB4].[USER_ID]
WHERE ((([TAB4].[INFO_MASTER]) Like "*__47__displayName=TAB3.[Display
Name]*"));

My Tab3 table have 2 colunms: User; Display Name
My Tab4 table have 2 columns: Info_Master; User_id

I want to update the Tab3.user with the Tab4.User_id where the Tab3.Display
Name is in the Tab4.Info_Master. But doesn't work.

This Info_master is a memo field that has lot of information, and I konw
that inside it has the text that i'm looking for.

Any help?
 
R

roadie.girl

you can't do an inner join on an update statement. you get around this
by doing a dlookup on the fields that are equal to fields of other
tables.
make sense?
 
J

John Nurick

Not so. Here's one I've just run on a test database:

UPDATE AddrNew INNER JOIN AddrNewTemp ON AddrNew.ID = AddrNewTemp.ID
SET AddrNewTemp.NewField = AddrNew.FirstName
WHERE AddrNewTemp.Street LIKE "*Street";
 
J

John Nurick

The problem is in here:

Like "*__47__displayName=TAB3.[Display Name]*"

Because it's all in quotes, Access is treating
TAB3.[Display Name]
as literal text and not the name of a field.

If you change that part of the query to

LIKE "*__47__displayName=" & TAB3.[Display Name] & "*"

then if TAB3.[Display Name] is
Nuno Guerra
it will find records where INFO_MASTER contains
__47__displayName=Nuno Guerra

Is that what you want?

Hi!

I have this SQL statment that doesn't work:
UPDATE TAB3 INNER JOIN TAB4 ON TAB3.User = TAB4.USER_ID SET TAB3.[User] =
[TAB4].[USER_ID]
WHERE ((([TAB4].[INFO_MASTER]) Like "*__47__displayName=TAB3.[Display
Name]*"));

My Tab3 table have 2 colunms: User; Display Name
My Tab4 table have 2 columns: Info_Master; User_id

I want to update the Tab3.user with the Tab4.User_id where the Tab3.Display
Name is in the Tab4.Info_Master. But doesn't work.

This Info_master is a memo field that has lot of information, and I konw
that inside it has the text that i'm looking for.

Any help?
Click to expand...
 
N

Nuno Guerra

Thanks, but sorry, it doesn't work with my Update Query.
I think that the problem may be in the Info_master column, it's a memo field.

John Nurick said:
The problem is in here:

Like "*__47__displayName=TAB3.[Display Name]*"

Because it's all in quotes, Access is treating
TAB3.[Display Name]
as literal text and not the name of a field.

If you change that part of the query to

LIKE "*__47__displayName=" & TAB3.[Display Name] & "*"

then if TAB3.[Display Name] is
Nuno Guerra
it will find records where INFO_MASTER contains
__47__displayName=Nuno Guerra

Is that what you want?

Hi!

I have this SQL statment that doesn't work:
UPDATE TAB3 INNER JOIN TAB4 ON TAB3.User = TAB4.USER_ID SET TAB3.[User] =
[TAB4].[USER_ID]
WHERE ((([TAB4].[INFO_MASTER]) Like "*__47__displayName=TAB3.[Display
Name]*"));

My Tab3 table have 2 colunms: User; Display Name
My Tab4 table have 2 columns: Info_Master; User_id

I want to update the Tab3.user with the Tab4.User_id where the Tab3.Display
Name is in the Tab4.Info_Master. But doesn't work.

This Info_master is a memo field that has lot of information, and I konw
that inside it has the text that i'm looking for.

Any help?
Click to expand...
Click to expand...
 
N

Nuno Guerra

The value i'm looking for is a part of the text. This is a memo field.
Although in each memo field have a text part that identifies to me one record.
Imagine a table with two fields: ID and MemoText; now try to return the ID's
that in the memo field has the explicit string that you are looking for.

I dont' know if dlookup is the right answer. If so, how can i do it?
 
J

John Nurick

Exactly what do you mean by "it doesn't work"? I don't see any reason
why the revised query shouldn't run. Whether it actually makes any
visible difference to the contents of TAB3 is a quite different matter.
One reason is that you haven't confirmed that the contents of
INFO_MASTER are likely to include strings of the form
__47__displayName=XXXXX
where XXXXX is the contents of the [Display Name] field in the other
table.

The other reason is that you have INNER JOINed the two tables on
TAB3.User = TAB4.USER_ID
and are then setting
TAB3.User = TAB4.USER_ID

In other words you are setting the field to the value it already has
(hence my remark about "visible difference")<g>.

What do you actually want to achieve?

Thanks, but sorry, it doesn't work with my Update Query.
I think that the problem may be in the Info_master column, it's a memo field.

John Nurick said:
The problem is in here:

Like "*__47__displayName=TAB3.[Display Name]*"

Because it's all in quotes, Access is treating
TAB3.[Display Name]
as literal text and not the name of a field.

If you change that part of the query to

LIKE "*__47__displayName=" & TAB3.[Display Name] & "*"

then if TAB3.[Display Name] is
Nuno Guerra
it will find records where INFO_MASTER contains
__47__displayName=Nuno Guerra

Is that what you want?

Hi!

I have this SQL statment that doesn't work:
UPDATE TAB3 INNER JOIN TAB4 ON TAB3.User = TAB4.USER_ID SET TAB3.[User] =
[TAB4].[USER_ID]
WHERE ((([TAB4].[INFO_MASTER]) Like "*__47__displayName=TAB3.[Display
Name]*"));

My Tab3 table have 2 colunms: User; Display Name
My Tab4 table have 2 columns: Info_Master; User_id

I want to update the Tab3.user with the Tab4.User_id where the Tab3.Display
Name is in the Tab4.Info_Master. But doesn't work.

This Info_master is a memo field that has lot of information, and I konw
that inside it has the text that i'm looking for.

Any help?
Click to expand...
Click to expand...
Click to expand...
 
N

Nuno Guerra

Thanks. You are correct.
I changed the sql statment and removed the inner join as you said.
Now it's working perfectly.
Thanks again.
:)

John Nurick said:
Exactly what do you mean by "it doesn't work"? I don't see any reason
why the revised query shouldn't run. Whether it actually makes any
visible difference to the contents of TAB3 is a quite different matter.
One reason is that you haven't confirmed that the contents of
INFO_MASTER are likely to include strings of the form
__47__displayName=XXXXX
where XXXXX is the contents of the [Display Name] field in the other
table.

The other reason is that you have INNER JOINed the two tables on
TAB3.User = TAB4.USER_ID
and are then setting
TAB3.User = TAB4.USER_ID

In other words you are setting the field to the value it already has
(hence my remark about "visible difference")<g>.

What do you actually want to achieve?

Thanks, but sorry, it doesn't work with my Update Query.
I think that the problem may be in the Info_master column, it's a memo field.

John Nurick said:
The problem is in here:

Like "*__47__displayName=TAB3.[Display Name]*"

Because it's all in quotes, Access is treating
TAB3.[Display Name]
as literal text and not the name of a field.

If you change that part of the query to

LIKE "*__47__displayName=" & TAB3.[Display Name] & "*"

then if TAB3.[Display Name] is
Nuno Guerra
it will find records where INFO_MASTER contains
__47__displayName=Nuno Guerra

Is that what you want?

On Thu, 12 Jan 2006 09:45:02 -0800, "Nuno Guerra"

Hi!

I have this SQL statment that doesn't work:
UPDATE TAB3 INNER JOIN TAB4 ON TAB3.User = TAB4.USER_ID SET TAB3.[User] =
[TAB4].[USER_ID]
WHERE ((([TAB4].[INFO_MASTER]) Like "*__47__displayName=TAB3.[Display
Name]*"));

My Tab3 table have 2 colunms: User; Display Name
My Tab4 table have 2 columns: Info_Master; User_id

I want to update the Tab3.user with the Tab4.User_id where the Tab3.Display
Name is in the Tab4.Info_Master. But doesn't work.

This Info_master is a memo field that has lot of information, and I konw
that inside it has the text that i'm looking for.

Any help?
Click to expand...
Click to expand...
Click to expand...
 
Top