SQL Question

[Thorsten Hohage]

Hi Bart,

I understand what you want ;-)

On 2009-01-30, at 22:58, Bart Pietercil wrote:
> This query is not accepted by Valentina but explains what I want to do
>
> update a set user_recid = u.recid FROM advita_import a
> join tbl_users u on a.employee_id = u.hr_db_id;
I often want to use the exact same style of query. Having two related  
tables and then update one field of one of these tables with value(s)  
from the other.


> some database accept this syntax but ok, so I considered making a view
yes, some more IMHO


> But it seems valentina views are only readonly so that won't work
> either.
Yes - and guess what - I asked the exact same question for exact same  
reason 3 days ago - "brother in way of thinking" :-)


> I know I can do this using subselects but just can't get my head
> around it. Probably just too late...

I was not able to use any style of subselects (because Valentina  
doesn't allow relations here - as many other DBs allow), so I ended up  
with two different options depending on the task

a) use a temp table, do the necessary join and export the result to  
the temp table
b) use a stored procedure


A now the stunning surprise at the end !!!!!!!!!!!!!!!! As always  
these guys are faster then you may think of a wish :-)


THIS statement works (for me surprisingly in the new v4 Beta)

	update insertJoin1 set newID =
		(select insertJoin2.toInsertId from insertJoin2 where  
insertJoin1.toLinkId = insertJoin2.linkedId)

someone must have done some excellent improvements.


regards,

Thorsten Hohage
-- 

Valentina Technology Evangelist
generic objects  GmbH - Leiter Solution Center Nord