Oracle query

CountingCrows

Quick question. Can the update field and the condition field be the same in an update statement?

I need the query to return the number of records newly updated, .i.e exclude the records that previously had the 'mytextvalue' value.

Here's what I'm trying to do but it doesn't work.

UPDATE mytable
SET mycolumn = 'mytextvalue'
WHERE mycolumn <> 'mytextvalue'

BrokenArrows

If you want to only update fields which are not already the new value then you just compare the field you are modifying. Not sure what your NEW_FLAG column is.

UPDATE mytable
SET mycolumn = 'mytextvalue'
WHERE mycolumn  <> 'mytextvalue'

croo

From here

CREATE TABLE employees_temp AS SELECT * FROM employees;
BEGIN
UPDATE employees_temp SET salary = salary * 1.05 WHERE salary < 5000;
DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' salaries.');
END;
/

CountingCrows

BrokenArrows wrote: »

If you want to only update fields which are not already the new value then you just compare the field you are modifying. Not sure what your NEW_FLAG column is.

UPDATE mytable
SET mycolumn = 'mytextvalue'
WHERE mycolumn  <> 'mytextvalue'

Sorry, my SQL should be as you posted, I've edit the OP. The statement above doesn't work, hence my confusion.

28064212

CountingCrows wrote: »

Sorry, my SQL should be as you posted, I've edit the OP. The statement above doesn't work, hence my confusion.

What do you mean by it "doesn't work"? Do you get an error, or does it just not update any rows?

If you do:

SELECT * FROM mytable WHERE mycolumn  <> 'mytextvalue'

do you get results?

CountingCrows

28064212 wrote: »

If you do:

SELECT * FROM mytable WHERE mycolumn  <> 'mytextvalue'

do you get results?

I get 0 rows returned. I think I've found my problem, NULL records aern't been included. I thought <> 'mytextvalue' would include NULLS but it seems I need to specify the IS NULL as a separate condition.

Ziycon

Try:

SELECT * 
FROM mytable 
WHERE mycolumn  <> 'mytextvalue'
OR NVL(mycolumn, 0) = 0

Giblet

CountingCrows wrote: »

I get 0 rows returned. I think I've found my problem, NULL records aern't been included. I thought <> 'mytextvalue' would include NULLS but it seems I need to specify the IS NULL as a separate condition.

If you need to use equality, turn off ansi nulls, otherwise is null or is not null are required. I would recommend using ansi nulls only though. It's clearer and non ansi nulls are in danger of being removed.