Select, Insert and Update un same query

[kolorasta]

i'm using access with sql server (.adp) and i have this query to insert records in a table:

INSERT table1 (field1, field2, field3)
SELECT (field1, field2, field3)
FROM table2
WHERE (condition)

this is an schema of my query...

What i want to do is to add and UPDATE sencence like this for every record selected in table2
UPDATE table2 SET field4=field4-1 WHERE table1.field1=table2.field1

how can i do it... where do i have to put the UPDATE sentence?

thanks and sorry for my poor english

 

[WayneRyan]

K,

INSERT table1 (field1, field2, field3)
SELECT (field1, field2, field3) 
FROM table2
WHERE (condition) [B][U]And[/U][/B]
Update Table2
Set field4 = field4-1 
WHERE field1 In (Select field1 From Table1)

Just kidding, remove the "And".
You can't combine a Select and Update query.

They have to be seperate.

Wayne

 

[kolorasta]

i didn't understand your answer... there is a little humor in your answer... i know that but your whole answer is a joke or you gave me the right answer for my problem, removing the "and" word???

thanks

 

[WayneRyan]

Sorry K,

As I said, I was just joking. You can't perform the Insert and Update in
one SQL statement. You'll have to break them into two statements.

Wayne

 

[kolorasta]

thank you for your answer...have a nice time