Tim

I get an error 700 with the syntax below.

update techniquesdocs T set _onDisk = True
from techniquesdocs T
join diskdocs D on T._Path = D._Path

Assuming its not a bug what's the correct syntax anyone.

Roy Lambert

Roy,

<< I get an error 700 with the syntax below.

update techniquesdocs T set _onDisk = True
from techniquesdocs T
join diskdocs D on T._Path = D._Path

Assuming its not a bug what's the correct syntax anyone. >>

A correlated sub-query will do what you want:

update techniquesdocs T set _onDisk = True
where T.Path=(SELECT D.Path FROM diskdocs D WHERE D._Path=T._Path)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

After many years ignoring it I started to learn SQL. I was getting to understand it and now I have to learn that gibberish. Wow talk about non-intuitive. When I win the lottery I'm taking out a contract on everyone involved in setting up the sql standard.

Rant over and thanks Tim


Roy Lambert

ps


Can we have examples of this sort of thing in the manual, or please recommend a good book

Roy Lambert

Roy,

<< Can we have examples of this sort of thing in the manual, or please
recommend a good book >>

I'll see about adding it as an example.

--
Tim Young
Elevate Software
www.elevatesoft.com

Does that mean we've lost the ability to update a bunch of columns at the
same time?
ie I've used the correlated sub-query in other implementations but just
hated it when I had to update more than one
column.  We have instances where we update many columns from the joined
table.  Thx.

> A correlated sub-query will do what you want:
>
> update techniquesdocs T set _onDisk = True
> where T.Path=(SELECT D.Path FROM diskdocs D WHERE D._Path=T._Path)
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>
>

Donat,

<< Does that mean we've lost the ability to update a bunch of columns at the
same time? >>

No, you just have to use a correlated sub-query for each column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ok, I see how that works but that will multiply processing time by n times
columns. (not counting the
less than appealing syntax with complex joins)  That's really unfortunate.

Your implementation on DBISAM is a real pleasure to work with.

Donat.


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:24EF9541-430C-46CF-B37E-4DF43F40B13C@news.elevatesoft.com...
> Donat,
>
> << Does that mean we've lost the ability to update a bunch of columns at
> the same time? >>
>
> No, you just have to use a correlated sub-query for each column.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Donat,

<< Ok, I see how that works but that will multiply processing time by n
times columns. (not counting the less than appealing syntax with complex
joins)  That's really unfortunate.

Your implementation on DBISAM is a real pleasure to work with. >>

I don't like the additional FROM syntax and didn't want to make a rush
decision, which is why it was left out.  However, it is the same as with SQL
Server, so I may end sticking with it for compatibility reasons.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I don't like the additional FROM syntax and didn't want to make a rush
decision, which is why it was left out.  However, it is the same as with SQL
Server, so I may end sticking with it for compatibility reasons. >>

As an alternative, have you considered multiple column assignment ?

Example:

update table1 t1
set (col1, col2, col3) =
(
 select col1, col2, col3
 from table2 t2
 where t2.col1 = t1.col1
)

Ole Willy Tuv