Generate script as an output line.....

[james0816]

I know you can generate a script as an output such as

select 'update table1 set item=''' || itemnum || ''' where item = ' || itemnum || ';'
from table2 where item='101';

however...i'm looking to bump it up a notch.

table two contains multiple records (4) with that itemnum so i need to use the distinct command to get only one occurance of it.

So...executing the above script will get me four lines of code. When I try to only get one line by using something like this:

select 'update table1 set item=''' || distinct itemnum || ''' where item = ' || distinct itemnum || ';'
from table2 where itemnum='101';

I get an error saying missing expression.

Is this even possible?

thx

 

[TonyGroves]

What do you mean by:[tt]
select 'update table1 ...[/tt]

That's not valid SQL! What's it designed to do?

 

[james0816]

the 'update table....' part will actually write a line of output....so you would select certain information and instead of displaying the actual record contents, I am trying to write additional code to execute later.

 

[TonyGroves]

UPDATE doesn't produce any result set. Have you tried it?

You'll have to rethink your approach.