Re: How To Update A Field ?

Hi

Your update has to go like this:

UPDATE WrongTable
SET WrongTable.employee_name = (
SELECT CorrectTable.employee_name
FROM CorrectTable
WHERE CorrectTable.employee_id = WrongTable.employee_id) WHERE EXISTS (SELECT null
FORM CorrectTable
WHERE CorrectTable.employee_id = WrongTable.employee_id);

And for your other question:

update table_a a
set (a.col1, a.col2, a.col) =
select b.colx, b.coly, b.colz
from table_b b
where b.key = a.key)
where exists (select null
from table_b b
where b.key = a.key);

Hope this helps.

Regards,

Michael Ringbo

Faro wrote:

> Hello Everyone,
>
> UPDATE WrongTable
> SET WrongTable.employee_name = (
> SELECT CorrectTable.employee_name
> FROM CorrectTable
> WHERE CorrectTable.employee_id = WrongTable.employee_id);
>
> The above script updates Field 1 in Table 1 with the information of
> Field 2 in Table 2 (both being of the same datatype). At the moment
> Field 2 basically overwrites all existing data with it's own data, and
> if it encounters data in Field 1 which it doesn't have an equivalent
> update it just replaces it with a NULL value (ie. wipes out the original
> value of Field 1 even if it has none of it's own to update).
>
> My question is, can an addition be made to the above query such that it
> would leave any existing data in Field 1 if it does not have a value to
> update it with ?. This is because if Field 1 is NOT NULL type then the
> query would not run as an error would be reported of an attempt to
> insert a NULL value into a NOT NULL field.
>
> Also, on a separate note, does anyone have a script to run which would
> update Fields 1,2, and 3 from Table 1 into Fields 1,2,3 of Table 2 all
> in one go (i.e., not updating one column at a time, rather one row at a
> time), does anyone have a handy script for such a task ?. You can assume
> that Field 1 is the key to join, and also the fields are of the same
> data types. Thank you so much for your time.
>
> Yours Sincerely,
> Angelica Veron,
> Faro_at_globalserve.net
Received on Mon Jun 28 1999 - 09:36:57 CEST