Re: How To Update A Field ?

From: Graham Miller <lgmiller_at_elmrd.u-net.com>
Date: Sun, 27 Jun 1999 13:20:09 GMT
Message-ID: <377e2501.27774627_at_news.u-net.com>


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 Sun Jun 27 1999 - 15:20:09 CEST

Original text of this message