Unite SELECT and UPDATE with the new value and length(old value)-1)

Databases
1

name nationality Tom American Joon Korean Jane Australian Natia Armenian Castro CubanI have myTable like the above.

Let’s suppsoe Natia become an American.

The code below will work for Natia.

UPDATE myTable SET nationality="American" WHERE name="Natia";

if I don’t know the last ending letter “n”, I have to find it and update it with the 2 SQL code and 1 php code below.

[code]SELECT right(nationality,1) AS ending from myTable WHRERE name=“Natia”;

$newNation=“America”.$ending;

UPDATE myTable SET nationality=“$newNation” WHERE name=“Natia”;[/code]Can I make it in one SQL code by your help?

The code below doesn’t work correctly but I hope it shows what I want.

UPDATE myTable SET nationality="$newNation".ending (SELECT right(nationality,1) AS ending from myTable WHRERE name="Natia") WHERE name="Natia";

The code below is another trial for it but failed.

UPDATE myTable SET left(country,length(nationality)-1)="America" WHERE name="Natia"; The code above tries updating nationality with the new value “America” and use the ending “n” of the previous ending value “n” in “Anmenian”.

2

Are you sure that’s how you want to approach it? That doesn’t work for as many countries as it does work.

What if the nationality changes to France, Scotland, England, Wales, Taiwan, Lebanon, Sweden, Canada, Japan, China, Vietnam…want me to go on? Oh, and don’t forget the Netherlands (i.e. the Dutch)

3

I am sorry, DaveMaxwellTeam, there’s something wrong in my question.

Actually, I just make the table for example.
but the example has something wrong.

Here’s what I want.

(1) I have the SELECT SQL below.

SELECT right(myColumn,1) AS ending FROM myTable WHERE id='11'

(2) And I have the PHP code below

$newValue_ending=$newValue.$ending;

(3) I have the UPDATE SQL below.

UPDATE myTable SET myColumn="$newValue_ending" WHERE id='11

Can I unite the (1), (2), and (3) code above into one SQL code with your help?
(Can I do adding the last letter of the old value to the new value in one UPDATE SQL?)

4

I THINK you should be able to do something like (you’ll need to test it though)

UPDATE myTable
   SET myColumn = CONCAT('New Value', right(myColumn,1)) 
 WHERE id = '11'

That being said, if it’s for nationality like in your original post, be aware that just adding “n” to the end of a country, you’re going to have more wrong than you’ll have right (pretty much, the only time that works is if the country name ends in an A, but even that doesn’t always work - ex, Canada => Canadian)

5

dave, you gotta remember that joon’s tables don’t actually include names or nationalities – those are fakes for the sake of example

6

I couldn’t tell for sure based on the initial post. It kinda looked like that based on his 2nd post, but I just wanted to be sure…

7

I apologize for my initial post which has something wrong.

When I have a question, I make it simpler for the point.
And I make a test table because the real table is much or too complex to explain it.
Usually the simplifying works fine for my question, but this time I failed in making a keypoint.

By the way, your code works fine.

Thank you, DaveMaxwell.

And Thank you, r937.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.