update field which results from joined query

User, date Message
Written by BubikolRamios
2 years ago
Category: Feature discussion
275 posts since Thu, 14 Jan 10 		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 !!!
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		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 (-:
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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?

HeidiSQL wrote: 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.
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		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.
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		Besides "which is used by many other users", everyone once and only once gets expirience how bad is not having backup.
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		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.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		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?
Written by kalvaro
2 years ago
455 posts since Thu, 29 Nov 07 		@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...
Written by kalvaro
2 years ago
455 posts since Thu, 29 Nov 07 		BTW... @Anse, how do you format quotes in this forum?

wrote: Test



> Test
> Test
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		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.
Written by BubikolRamios
2 years ago
275 posts since Thu, 14 Jan 10 		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.
Written by ansgar
2 years ago
4020 posts since Fri, 07 Apr 06 		@kalvaro: quoting is done so (without spaces): [ quote = PreviousUser ] text to quote [ / quote ]
 

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