Re: How To Update A Field ?

From: Angelica Veron <Faro_at_globalserve.net>
Date: Sun, 27 Jun 1999 21:43:08 -0500
Message-ID: <3776E13C.1905EB77_at_globalserve.net>


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)
WHERE
    EXISTS
        (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,
is it necessary to put that in, or not ??.. I thought of placing this line 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. But if your query avoids this without the need for that last statement then
please let me know, once again thank you so much for your time.

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

Original text of this message