Re: How To Update A Field ?

Hello Graham,

    Thank you so much for your help, I have tried this query and it has worked, but I am just wondering what the effects are of adding one more statement to the end of that query...

UPDATE WrongTable

     SET WrongTable.employee_name =
        (SELECT CorrectTable.employee_name
         FROM CorrectTable
         WHERE CorrectTable.employee_id = WrongTable.employee_id)

        (SELECT CorrectTable.employee_name
         FROM CorrectTable
         WHERE CorrectTable.employee_id = WrongTable.employee_id
         AND c1.employee_name IS NOT NULL);

        You can see the extra line at the end is suggested... my question is,

Graham Miller wrote:

> Hello Angelica,
> the easiest way is to just add a 'where' clause to your Update
> statement to only hit rows which match...
>
> UPDATE WrongTable
> SET WrongTable.employee_name =
> (SELECT CorrectTable.employee_name
> FROM CorrectTable
> WHERE CorrectTable.employee_id = WrongTable.employee_id)
> WHERE
> EXISTS
> (SELECT CorrectTable.employee_name
> FROM CorrectTable
> WHERE CorrectTable.employee_id = WrongTable.employee_id;
>
> This will only hit matching rows.
>
>
> graham
>
> On Sun, 27 Jun 1999 03:26:08 -0500, Angelica Veron
> <Faro_at_globalserve.net> 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 - 04:43:08 CEST