Re: How To Update A Field ?

Angelica Veron schrieb:

> 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).

UPDATE WrongTable wt
SET wt.employee_name = (SELECT ct.employee_name

                        FROM CorrectTable ct
                        WHERE ct.employee_id = wt.employee_id)
WHERE exists ( SELECT 'x'
               FROM
                   CorrectTable ct1,
                   WrongTable wt1
               WHERE ct1.employee_id = wt1.employee_id);

the additional WHERE-Clause reduces the rows in the UPDATE-Statement to rows with the employee_id which also exist in the CorrectTable

>
>
> 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.

UPDATE WrongTable wt
SET (wt.val1, wt.val2, wt.val3) = (SELECT ct.val1, ct.val2, vt.val3

                                   FROM CorrectTable ct
                                   WHERE ct.employee_id = wt.employee_id)
WHERE exists ( SELECT 'x'
               FROM
                   CorrectTable ct1,
                   WrongTable wt1
               WHERE ct1.employee_id = wt1.employee_id);

I did'nt try these statements, but they should work...

Hope it will help you, let me know about it

Regards,

Stephan Born

--
---------------------------------------------------------------
Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------