Re: One SQL statement to update multiple tables?

On Tue, 21 Jul 1998 00:13:09 -0400, "Chris Garland" <cgarland_at_nr.infi.net> wrote:

>Can anyone think of a solution (i.e., strange and convoluted SQL
>statement, some funky Oracle trick,etc.) that will allow us to execute
>only one ODBC call to update multiple tables?

Sure.

Create a KEYTABLE table with 2 columns:

• Key (NUMBER) and Value (NUMBER)

and fill it with some values, namely:

• Insert ... 1,0
• Insert ... 2,0
• Insert ... 3,0

then create a trigger on it:

	Create trigger ... on update
		referencing old as old new as new ...
	begin
		if :old.key = 1 then 
			update ...
			update ...
		end if;
		if :old.key = 2 then 
			update ...
			update ...
		end if;
		....
	end;

then simply do (via ODBC)

        UPDATE KEYTABLE SET value=1-value where key=1;

and you're done.

>
>Thanks,
>Chris Garland
>garlandc_at_volvo.com

--
Mauro.

(My return address is intentionally invalid; ROT13 is required. As someone already told this audience, my responses are not to be considered official technical support or advice.) Received on Tue Jul 21 1998 - 10:10:41 CDT