Hi

I'm trying to make an update query for multiple data and no success. The problem is this.

Names
Agent mary
ag. roger
agentus jhonny
ca. mary
ca. roger
ca. jhonny

I want to transform ca. mary in agent mary, ca. roger in ag. roger, ca. jhonny in agentus jhonny in a single update query.

The problem is that the 'where' clause can be used only once.

Need more information...

Need more information...

Ok. I have a table like this.

name
ag. jhon
agent mary
ag. jhon
agentus roger
agentus roger
ca. mary
ca. jhon
ca. roger

Yes these are duplicates.

I want ot make an update query to change all the ca. agents to the exactly corespondent agent like this: ca. mary to agent mary, ca. jhon to ag. jhon and ca. roger to agentus roger. All this in a single update query.

Need more information...tony,

your profile page on FACEBOOK suggests that there are almost 600 million profiles on that site. yours is like record 570,###,### or something. isn't that incredible? i rarely use that stuff, and I'll spend my life wondering why something like that is so popular. other than being "something to do" :p

Ok. I have a table like this.

name
ag. jhon
agent mary
ag. jhon
agentus roger
agentus roger
ca. mary
ca. jhon
ca. roger

Yes these are duplicates.

I want ot make an update query to change all the ca. agents to the exactly corespondent agent like this: ca. mary to agent mary, ca. jhon to ag. jhon and ca. roger to agentus roger. All this in a single update query.i don't guarantee that this is right bibi, and besides, tony has already got the thread, but i'll make a suggestion (qry SQL sample):UPDATE table SET
[name] =

IIF(

instr([name], "ca.") = 0, [name],

left(

dlookup(

"name",
"table",

"instr([name], 'ca.') = 0 AND
right([name], len([name]) - instrrev([name], ' ') =
right([name], len([name]) - instrrev([name], ' ')"

),

instr([name], " ") - 1

)

& " "

& right([name], len([name]) - instrrev([name], " ")

)also, please don't use NAME as a field name. conflicts abound later on. reserved word alert!

other than being "something to do"

And you are here..... :)

I don't do much facebook, I'm to busy lately however it was very handy when I was "Between Contracts" as it were,,,,

[I]
And you are here..... :)
,

what r u talking about?

i don't guarantee that this is right bibi, and besides, tony has already got the thread, but i'll make a suggestion (qry SQL sample):UPDATE table SET
[name] =

IIF(

instr([name], "ca.") = 0, [name],

left(

dlookup(

"name",
"table",

"instr([name], 'ca.') = 0 AND
right([name], len([name]) - instrrev([name], ' ') =
right([name], len([name]) - instrrev([name], ' ')"

),

instr([name], " ") - 1

)

& " "

& right([name], len([name]) - instrrev([name], " ")

)also, please don't use NAME as a field name. conflicts abound later on. reserved word alert!

Hi

I tried like u said but it's not working. To sumarize the problem I have I will say that all that data and updates resume to one question:
How can somebody replace different data in a column with one single update query?

How can somebody replace different data in a column with one single update query?

i just answered it. please don't ask things over and over again. we hear the first time.

and so...if you want to upload a database here for prying eyes, you're welcome to do so. in order to fix the problem, that is all i left to offer myself. others may somethings else for you though. btw, just saying that "it didn't work" is never a good response. it doesn't tell me at all WHY it didn't work. for all i know, you could have simply cut and pasted by code into your sql window and then tried to run it. if that's the case, it obviously won't work as it's not meant to be taken literally. if you made the modifications necessary though, i would now have to see it in real time to diagnose where the bug is at

what r u talking about?

Oh! I see..... I got the wrong end of the stick as usual....

I'm an early adopter... of most tecno thingess

i just answered it. please don't ask things over and over again. we hear the first time.

and so...if you want to upload a database here for prying eyes, you're welcome to do so. in order to fix the problem, that is all i left to offer myself. others may somethings else for you though. btw, just saying that "it didn't work" is never a good response. it doesn't tell me at all WHY it didn't work. for all i know, you could have simply cut and pasted by code into your sql window and then tried to run it. if that's the case, it obviously won't work as it's not meant to be taken literally. if you made the modifications necessary though, i would now have to see it in real time to diagnose where the bug is at


Ok. First of all I made the modification and the error was about the number of arguments in the IIF function. Second of all if you want to help then I'll say that after that I tried to use a definition table for the names and used something like this but 0 rows changed:

Definition table
1st names 2nd names
ag. 1 ca. 1
agentus 2 ca. 2
agent 3 ca. 3

The definition table is lsft join with the names from the sales table that contains all data. (ag. 1, agentus 2, agent 3, ca. 1,ca. 2, ca. 3) in the same column.
I used this
UPDATE DEFINITION_TABble LEFT JOIN sales_table ON DEFINITION_TABLE.1stnames = sales_table.name SET sales_table.name = definition_table.1stnames
WHERE (((sales_table.name)=definition_table.2ndnames));