Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

update field which results from joined query

BubikolRamios's profile image BubikolRamios posted 10 years ago in Feature discussion Permalink
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 !!!
BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
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 (-:
ansgar's profile image ansgar posted 10 years ago Permalink
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.

BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
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.
BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
Besides "which is used by many other users", everyone once and only once gets expirience how bad is not having backup.
BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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?
kalvaro's profile image kalvaro posted 10 years ago Permalink
@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...
kalvaro's profile image kalvaro posted 10 years ago Permalink
BTW... @Anse, how do you format quotes in this forum?

Test



> Test
> Test
BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
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.
BubikolRamios's profile image BubikolRamios posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
@kalvaro: quoting is done so (without spaces): [ quote = PreviousUser ] text to quote [ / quote ]
Jawahar's profile image Jawahar posted 3 months ago Permalink

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

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.