update field which results from joined query

forinstance

select b.text
from a join b on a.id = b.id

In resultset I would like to manualy correct 'text' field.
Heidi SQL does not let do that. Doh I see no reason for it
as I suppose HSQL knows from which table that field comes.

I know that can be done somehow , I saw it beeing done in some similar software, I think it was/is named TOAD SQL or something similar.


That feature would be most useful !!!

P.S.

Ok, the most simple solution, executable at glance, for that would be:

1.Update b.text where text = 'selected text in grid'
2. pop up: "Warning > 1 record will be updated in b table"
Abort/continue
3. Click Abort or Continue

Since it is expected that smart guys/girls are using HSQL, knowing their databases, that would be OK (-:

UPDATE b SET text='newvalue' WHERE text='oldvalue'

Oh you are crazy, man. If that's safe enough for you you can do that by query, but I won't do that in HeidiSQL which is used by many other users, expecting a security blocker for unsafe automated stuff.

I guess you got this error and didn't read it?

Grid editing error:
Selected columns don't contain a sufficient set of key columns to allow editing. Please select primary or unique key columns, or just all columns.

Nope:
Grid editing - more than one table involved.

But to enlight you, how big pain in ass (not having this), can be: real example;

http://www.shrani.si/f/46/3H/1BphdGqx/tmp.jpg

There is one null in right most column, but there are a couple more. The reason for that is that 'Nucifraga caryocactes' is badly spelled

So you see I have to write for each column with null
select from galery_1_latin where species = 'copy badly spelled stuf' in addition to finding around how is rightly spelled.

I',m sure toad sql did not invent that just for fun.

Besides "which is used by many other users", everyone once and only once gets expirience how bad is not having backup.

to make extra point, see this example

update
galery_1 as a
left join tezaver t on t.id_tezaver = a.id_tezaver and t.l2 = 'la'
left join galery_1_latin l on a.id_galery = l.id_galery
left join tezaver t1 on l.species = t1.term and t1.l2='la'
set a.id_tezaver = t1.id_tezaver
where t.id_tezaver is null
and a.id_tezaver <> -1

I mean, no warning form HSQL, but without realy good thinking before running this & possibly having backup the damage
would be bigger than with implementing upper idea.

I'm nearly sure that Toad won't let you edit the text field of a result from your example query above:

select b.text from a join b on a.id = b.id

Also it doesn't matter much if some other client does something broken, that does not mean Heidi should do the same broken stuff. But you were also just guessing about Toad. Could you please shed some light onto that guess?

What about just using HeidiSQL's data tab for editing galery_l_latin, instead of using a complex join query?

@BubikolRamios: you've made the wrong assumption that "HeidiSQL knows from which table that field comes". I've never heard that the MySQL server sends any piece of metadata with such info. Actually, fields don't even need to come from a column since they can be calculated in many different ways, from aggregate functions like SUM() to concatenations, subqueries...

BTW... @Anse, how do you format quotes in this forum?

Test

> Test
> Test

Nope, not guessing ...

So, I downloaded it again, instaled it ...

http://www.toadworld.com/Freeware/ToadforMySQLFreeware/tabid/561/Default.aspx

tols/master/detail browser
add table from query/paste query (the one from above, with many joins) there/under query/ click green button so it executes and you see resultset

If you try to edit now, it does not let you just like HSQL.

Now, on the bottom of resultset you have a semaphore(grid is editable tooltip) and next to it black drop down button --> define temporary index

Having done that as needed, it does not complain anymore and lets you edit.

Far more complicated compared to what I suggested for HSQL, but it works.

What about just using HeidiSQL's data tab for editing galery_l_latin, instead of using a complex join query?

Point beeing: query returns like 100 records, from tables each possibly having zilion of records. And wiewing each table separately does not tell you anything.
My suggestion was towards , not loosing great amount of time.

P.S

Having done that as needed, it does not complain anymore and lets you edit.

Tested the change with mysql query browser(more familar with it), and the change is there , in db.

@kalvaro: quoting is done so (without spaces): [ quote = PreviousUser ] text to quote [ / quote ]

SELECT 0 myselect,l.uid,l.company_uid,l.empmaster_uid,e.empmaster_uid,d.empmaster_uid,n.empmaster_uid,s.empmaster_uid,su.empmaster_uid,gr.empmaster_uid, EmpCode,firstname,LastName,Joiningdate,EmpStatus,CmpPhoneNo,e.name Location_uid,d.name Department_uid,n.name Designation_uid,CompanyEmaiID,UserID,EmpPassword,Salary,EmpCurrency,s.name sitemaster_uid,su.name Supervisor_uid,gr.name Grade_uid FROM empmaster l INNER JOIN location e ON l.empmaster_uid=e.empmaster_uid AND l.Location_uid=e.uid INNER JOIN department d ON l.empmaster_uid=d.empmaster_uid AND l.Department_uid=d.uid INNER JOIN designation n ON l.empmaster_uid=n.empmaster_uid AND l.Designation_uid=n.uid INNER JOIN sitemaster s ON l.empmaster_uid=s.empmaster_uid AND l.sitemaster_uid=s.uid INNER JOIN supervisor su ON l.empmaster_uid=su.empmaster_uid AND l.Supervisor_uid=su.uid INNER JOIN grade gr ON l.empmaster_uid=gr.empmaster_uid AND l.Grade_uid=gr.uid