Hello all

I have this query

select * from table1 a1
left join table2 a2 on a1.field1 = a2.field1
where a1.date >= '2006-01-02' and a1.date <= '2006-01-03' and a1.field2 > 0

Now I want to update two tables (table1 and table2) using this query as a filter.
Something like this:

update table1 a1, table2 a2
set a1.field2 = value, a2.field2 = value
where [I don't know what] in
select * from table1 a1
left join table2 a2 on a1.field1 = a2.field1
where a1.date >= '2006-01-02' and a1.date <= '2006-01-03' and a1.field2 > 0

I think this is confusing.

Any help is apreciated. Thank you all.

Regards.

"Knifebright" <knifebright@gmail.com> wrote in message
news:3A33124F-6761-4379-868B-B3A65BDE6423@news.elevatesoft.com...
> Hello all
>
> I have this query
>
> select * from table1 a1
> left join table2 a2 on a1.field1 = a2.field1
> where a1.date >= '2006-01-02' and a1.date <= '2006-01-03' and a1.field2 >
0
>
> Now I want to update two tables (table1 and table2) using this query as a
filter.
> Something like this:
>
> update table1 a1, table2 a2

AFAIK, it is not possible to update multiple tables in a single statement. I
would use a script, run your first query INTO Memory\mytable followed by two
update statements, one for each table, JOINing the memory table as required.

Robert