Convert MySQL Select query into an Update query

Question

I am using MySql to update the last line ( Member_id = 140) of database users of a column Sponsor_Parent with the result of a Selection of the last line of the Column (Sponsor_username = Test91) to find their Sponsor_username

That is the Last line Sponsor_username is Test91 and want to update with the a selection of the value of their sponsor username = Test1A

Thus putting in Test1A into Sponsor_Parent of last line Member_id = 140

My Select works fine but I cannot convert it into a working Update query.

These Select queries work fine:

SELECT member_id, username, sponsor_username from users WHERE username = 'Test91'

This is a Manual Direct Input using the last line value. Returns: 15, Test91, Test1A

Now I take it a step further and select the value which works fine

SELECT member_id, username, sponsor_username from users WHERE username = (Select sponsor_username From users ORDER BY member_id DESC LIMIT 1)

Returns: 15, Test91, Test1A

This also works with a manual variation

Select Member_id, username, sponsor_username From users Where sponsor_username = 'Test91' in (sponsor_username) AND 'Test91' in (username)

Returns: 15, Test91, Test1A

When I use this Command as an Update

Update users Set sponsor_parent = (SELECT sponsor_username from users WHERE username = (Select sponsor_username From users ORDER BY member_id DESC LIMIT 1))

I get the Error:

#1093 - Table 'users' is specified twice, both as a target for 'UPDATE' and as a separate source for data

So if I get rid of the duplicate users with a kind of Join command

Update users s1 Set sponsor_parent = (SELECT s1.sponsor_username WHERE s1.username = (Select s1.sponsor_username ORDER BY s1.member_id DESC LIMIT 1))

It updates every row with a Null value

Not sure how to get the last line updated with Sponsors, Sponser name in Sponsor_parent column

User Table Layout:

Member_id	username	firstname	lastname	sponsor_id	sponsor_username	sponsor_new	Sponsor_parent	Spon_matched
15	Test91	My	Test91	0	Test1A	0	0
140	TestD3	My	TestD3	15	Test91	4	0

User Table structure

What I want is in the Last line of every Insert is the Parent name of the Sponsor_username So when a new user joins ( Username ) the Introducer is listed in (Sponsor_Username ) and if there are two generations list the Introducer of the Introducer. ( ie is the Introducers Parent Username listed in Sponsor_parent ) I am looking for an Update / Select command

Final Layout desired

So at Member 15 Test1A introduces Test91 who goes on to Introduce testD3 at Member ID 140. So I want the grandparent username if possible on any new member username, otherwise Null entry Insert into Sponsor_parent Column on the last line entry

Problem I am facing is I am selecting the Grandparent name and then trying to update the last line with the selection all from within the same table.

OK I nearly solved my own question This works update users as u1 inner join ( SELECT sponsor_username from users where username = (SELECT sponsor_username from users ORDER BY member_id DESC LIMIT 1)) as u2 set u1.sponsor_parent = u2.sponsor_username where member_id = "140"

All I need to do now is replace the "140" last line Member_id with something like "ORDER BY member_id DESC LIMIT 1" but I can't get the syntax right to update the last line only

Answer 1

Finally Solved the Update command of the Sponsors Sponsor entry

The Problem I had was Doing an Update with a Select command using two references to the same table in the command This caused a Duplicate problem giving this error #1093 - Table 'users' is specified twice, both as a target for 'UPDATE' and as a separate source for data

I had to select the last line from a table 'users' and use this to lookup the sponsor name in another column from the same table and find out who there sponsor was in order to update a new column in the same table.

So what I have with this Query command is a Nested Inner join command including a Vertical lookup (Vlookup) using the last line value command - ORDER BY member_id DESC LIMIT 1

The final solution Update command is update users as u1 inner join ( SELECT sponsor_username from users where username = (SELECT sponsor_username from users ORDER BY member_id DESC LIMIT 1)) as u2 inner join ( SELECT member_id from Users Where member_id = member_id ORDER BY member_id DESC LIMIT 1) as u3 ON u1.member_id = u3.member_id set u1.sponsor_parent = u2.sponsor_username