want to understand how update works in mysql

Question

New to mysql need help to understand this

SELECT * FROM roles1 r1 ;

result 
id role
1  ROLE_SELLER1
1  ROLE_SELLER2
1  ROLE_SELLER3
2  ROLE_SELLER4
2  ROLE_SELLER5

SELECT * FROM roles r1 ;

result
id role
1  ROLE_SELLER1
2  ROLE_SELLER4

UPDATE roles1 r1 ,roles r SET r.role=r1.role WHERE r.id=r1.id ;

after the above update query roles table does not change at all.i thought it should be like

SELECT * FROM roles r1 ;

   result
   id role
   1  ROLE_SELLER3
   2  ROLE_SELLER5

SELECT r.,r1. FROM roles r RIGHT JOIN roles1 r1 ON r.id = r1.id;

    result 
    id role                                id role
    1  ROLE_SELLER1         1  ROLE_SELLER1
    1  ROLE_SELLER1         1  ROLE_SELLER2
    1  ROLE_SELLER1         1  ROLE_SELLER3
    2  ROLE_SELLER4         2  ROLE_SELLER4
    2  ROLE_SELLER4         2  ROLE_SELLER5

update roles r join roles r1 on r.id = r1.id set r.role = r1.role; i thought when i use this it should update roles table like

   result
    id role
    1  ROLE_SELLER3 (last matched value from roles1 table id 1)
    2  ROLE_SELLER5(last matched value from roles1 table id 1)

Thanks in adv...

http://stackoverflow.com/questions/2334712/how-to-update-from-a-select-in-sql-server — drowned, Apr 24 '17 at 22:18

score 0 · Answer 1 · answered Apr 25 '17 at 00:59

While I cannot really answer your question because I cannot understand it I can share with you a few things that might help clarify your understanding:

1. For any query, whether it is an update or select you will almost never do the following:

select * from table1, table2 ....
update * from table1, table2 ....

The reason why you'll never do that is because joining tables with just a comma does a full cross join every row to every row. It results not only in craziness, but way too much craziness. So just avoid such things. About the only time it's ever used is when you're joining a table with a single row and value...like a business_date...or some other single value table. Instead you should use the following pattern for all joins:

select * 
from table1 t1
join table2 t2 on t1.id = t2.id
...

Using the above pattern avoids full joins and all the ridiculousness that goes with them. You should always join on something, remember the key word on. You want to always be specifying how tables should relate to each other in all queries - updates,deletes,selects. Remember this it is very key.

2. Avoid id columns that aren't unique. I really shouldn't see tables with id's in them where the ids are not unique. Add a unique key to your table or make it the primary key and then you won't end up with duplicate id confusion.

Now on to your particular query, I'm going to take a total stab here since again your questions verbiage is difficult to parse made more-so by the values you provide. I'm going to assume you want the values of table 2 to update the values of table 1. In other words table1 currently looks like:

id role
1  ROLE_SELLER1
1  ROLE_SELLER2
1  ROLE_SELLER3
2  ROLE_SELLER4
2  ROLE_SELLER5

and after the update you're going to want to see it look like the following because it will have taken the values of table2 and matched them to table1:

id role
1  ROLE_SELLER1
1  ROLE_SELLER1
1  ROLE_SELLER1
2  ROLE_SELLER4
2  ROLE_SELLER4

The update statement for such a transformation would look like this:

update roles r
join roles r1 on r.id = r1.id
set r.role = r1.role;

Anyway I'm not sure why professors at University teach joins with commas....but don't do it and you'll be a master of SQL in no time. Good luck!

thanks a lot for the quick reply.. but need more clarification..i have edited the question please check . — user3093845, Apr 27 '17 at 06:32

want to understand how update works in mysql

1 Answers1