I want to update the value of  table1.fieldA  with the value of  table2.fieldA from the record of table2 where the value of table1.fieldB is between the values of table2.fieldC AND table2.fieldD


something like

set table1.fieldA=tabel2.fieldA where table1.fieldB between table2.fieldC AND table2.fieldD

table1 and table2 has no field to JOIN on

is this a question from a DUMMY?

Paul.

Paul

>I want to update the value of table1.fieldA with the value of table2.fieldA from the record of table2 where the value of table1.fieldB is between the values of table2.fieldC AND table2.fieldD
>
>
>something like
>
>set table1.fieldA=tabel2.fieldA where table1.fieldB between table2.fieldC AND table2.fieldD
>
>table1 and table2 has no field to JOIN on

I haven't got a pair of tables I can test with but try

UPDATE
table1
JOIN table2 ON table1.fieldB BETWEEN table2.fieldC AND table2.fieldD
SET table1.fieldA = table2.fieldA

I'm using EWlevateDB these days and I'd use a subselect so I'm guessing at DBISAM syntax.

Roy Lambert

Paul



I also had a quick hunt through the newsgroup for UPDATE and JOIN and found this post

Chris,

> What is the correct SQL syntax ? They both have a unique index by field
> 'Name' that should be the same in both tables

UPDATE table1
SET Address = table2.Address
FROM table1
LEFT OUTER JOIN table2 ON table2.Name = table1.Name

You may also use correlation names:

UPDATE table1 a
SET Address = b.Address
FROM table1 a
LEFT OUTER JOIN table2 b ON b.Name = a.Name

Ole Willy Tuv




Roy Lambert