Could you be more specific? What's an auxiliary construct? It's not a term I'm familiar with in an Oracle context.

If it only has one column, how could you tell whether the values were exchanged or not? There is no inherent order to how rows will be returned by a query.

SELECT mycolumn
FROM mytable
ORDER BY mycolumn asc;

and

SELECT mycolumn
FROM mytable
ORDER BY mycolumn desc;

will technically do what you have asked.

Like this?:

UPDATE your_table SET your_column = decode(your_column,'NATO','EU','EU', 'NATO', your_column)
WHERE your_column IN ('NATO', 'EU')

DECODE, CASE, REGEXP_REPLACE etc. are not allowed.

but I want what's the auxiliary constructs

I think we've established that "auxiliary constructs" is not a phrase any of us are familiar with, at least in the context of SQL. From a little light Googling I gather it's a phrase originating in mathematics (geometry, topology); also known as auxiliary lines.

I'm not sure if that helps clarify anything, but I'm guessing that the original question uses "auxiliary constructs" to mean "user defined functions".

Cheers, APC

blog: http://radiofreetooting.blogspot.com

My question is, are there in the code below auxiliary constructs ??
if yes what is it ?? or may some tell me what's the auxiliary constructs in sql -statment ? explain to me pls

UPDATE Is_member
SET Organization= 'EU11'
WHERE Organization= 'NATO';

/*Change all EU posts in the table in NATO Is_Member */

UPDATE Is_member
SET Organization= 'NATO'
WHERE Organization= 'EU';
/* Change all EU11 Is_Member posts in the table in EU */

UPDATE Is_member
SET Organization= 'EU'
WHERE Organization= 'EU11';

Like everyone else, I am a bit puzzelled by "auxiliary constructs", but since, as michaels and Dave demonstrated, the update can be done in a single statement, I would guess that perhaps the:

UPDATE Is_member 

SET Organization= 'EU11'

WHERE Organization= 'NATO';

query is an auxiliary construct, and perhaps even the query:

UPDATE Is_member 

SET Organization= 'EU'

WHERE Organization= 'EU11'; 

On the other hand, another method with a real construct (not sure if it is auxiliary or not) would be something like:

CREATE TABLE member_temp AS

SELECT * FROM is_member WHERE 1=2;


INSERT INTO member_temp

SELECT 'NATO' FROM is_member

WHERE organization = 'EU';


INSERT INTO member_temp

SELECT 'EU' FROM is_member

WHERE organization = 'NATO';


TRUNCATE TABLE is_member;


INSERT INTO is_member

SELECT * FROM member_temp;

but that is a really bad way to do it.

Cheers
John