I need to rename differents rows in the same field, but names are different. (1 Viewer)

[pawson]

Hi everyone

I need to rename a list of softwares that are incorrect like :
- Microsoft Professional 2019 - es (Remove -es)
- Microsoft Professional 2016 - us (Remove -us)

If it's possible to do that in just one query, but the program doesn't accept different UPDATE and SET in the same query. Why no? How can i fix it?

Thanks

 

[vba_php]

yes it does. SET is part of an UPDATE statement. the word SET is a "qualifier" for including field names that have to be updated. you must be doing something incorrectly. but you really can't do what you want to do in a practical manner in just one query, especially if the characters you want to remove or change are different in every record. can you identify anything, from record to record, that remains consistent or patternized that you can use for identification purposes? if you can, then you can write query functions. if you can't, more than likely you will have to code the solution

 

[CJ_London]

what query have you tried?

 

[MajP]

Did you try a comma?

UPDATE Person.Person
 Set FirstName = 'Kenneth'
    ,LastName =  'Smith'
 WHERE BusinessEntityID = 1

 

[pawson]

Thanks via-php.
So what you are telling me is make different querys and updating my data base query by query??
I didn0t understand how to code the solution

 

[pawson]

Did you try a comma?

UPDATE Person.Person
Set FirstName = 'Kenneth'
    ,LastName =  'Smith'
WHERE BusinessEntityID = 1

In the same field bro, first name and last name are different fields
I need to doit in the same.

 

[theDBguy]

In the same field bro, first name and last name are different fields
I need to doit in the same.

Hi. Are you basically just trying to strip out the part after the last dash?

 

[moke123]

How about nested replace statement
something like
replace(replace(yourfield,"_es", ""),"-Us","")

 

[MajP]

In the same field bro, first name and last name are different fields
I need to doit in the same

Well Brofessor, how about you give it a try

set SomeField = replace([someField],"2019 -es","2019"), someField = replace([somefield],"2016 - us","2016")

 

[vba_php]

Thanks via-php.
So what you are telling me is make different querys and updating my data base query by query??
I didn0t understand how to code the solution

Pawson,

I'm saying look for patterns. for instance, if your data is like this:

- Microsoft Professional 2019 - es
- Microsoft Professional 2007 - us
- Microsoft Professional 2002 - ru
- Microsoft Professional 2000 - en

and, like you said, you want to remove the "-es" and "-us" and the those right-side letters for EVERY record you have, and those letters change for each record but are always 5 characters long, this will work fine for one query:

UPDATE yourTable SET yourTable.yourField = Left([yourTable],Len([yourTable])-5);

backup your table before you try this!

however, if your records always have the letters "- es" on the right side, then MajP's solution works fine.

 

[CJ_London]

UPDATE yourTable SET yourTable.yourField = Left([yourTable],Len([yourTable])-5);

This code won't work, I'll leave vba to correct it

 

[vba_php]

This code won't work, I'll leave vba to correct it

oh really? then how come, via a test in 2016, it works fine? let the person asking the question decide what works and what doesn't. that's why multiple people answer every question, isn't it?

 

[CJ_London]

did it now? I'll give you a clue - what is the length of your table?

 

[vba_php]

did it now? I'll give you a clue - what is the length of your table?

come on, let us not get into an argument. we're here to help others, aren't we? we can argue anywhere else. don't turn into richard on me. there can only be one of him =)

 

[CJ_London]

i'm not getting into an argument - you provided example code that is clearly wrong in a big way and even claim it works for you - and you cant even be bothered to apologise and correct it. As far as I'm concerned that is the end of the discussion. Others can make their own minds up about your abilities and persona - many already have.

 

[pawson]

Pawson,

I'm saying look for patterns. for instance, if your data is like this:

- Microsoft Professional 2019 - es
- Microsoft Professional 2007 - us
- Microsoft Professional 2002 - ru
- Microsoft Professional 2000 - en

and, like you said, you want to remove the "-es" and "-us" and the those right-side letters for EVERY record you have, and those letters change for each record but are always 5 characters long, this will work fine for one query:

UPDATE yourTable SET yourTable.yourField = Left([yourTable],Len([yourTable])-5);

backup your table before you try this!

however, if your records always have the letters "- es" on the right side, then MajP's solution works fine.

Yeah it's a nice solution, but not necessary for my case, because these are some of example. I have another registres that have more lenght than 5, so what can I do?
For example:
Microsoft Professional 2019 - en.us-proof
Microsoft Professional 2019 - en.en
....

Please help

 

[NauticalGent]

Have you responded to post #7 from @theDBguy ?

Edit: That was a rhetorical question, what I should have said was "You need to respond to @theDBguy 's question in post #7

 

[vba_php]

Yeah it's a nice solution, but not necessary for my case, because these are some of example. I have another registres that have more lenght than 5, so what can I do?
For example:
Microsoft Professional 2019 - en.us-proof
Microsoft Professional 2019 - en.en
....

Please help

more than likely u will need code. Anyone here is smart enif to provide u some. If u want me to do it i will be back to my machine in 2 hours

 

[pawson]

Well Brofessor, how about you give it a try

set SomeField = replace([someField],"2019 -es","2019"), someField = replace([somefield],"2016 - us","2016")

Why doesn't detect the string? It wants to update whole my rows. I don't know why?

 

[MajP]

Before
Copy Of tblSoftware

ID	software
1​	Microsoft Professional 2019 - en.us-proof
2​	Microsoft Professional 2019 - en.en
3​	Microsoft Professional 2019 - es
4​	Microsoft Professional 2016 - us

UPDATE tblSoftware SET tblSoftware.software = cleanversion([software]);

after
tblSoftware

ID	software
1​	Microsoft Professional 2019
2​	Microsoft Professional 2019
3​	Microsoft Professional 2019
4​	Microsoft Professional 2016

Public Function CleanVersion(strSoftware As Variant) As String
  If Not IsNull(strSoftware) Then
    CleanVersion = Trim(Split(strSoftware, "-")(0))
  End If
End Function

Any more questions Brochild?