Update Query Question

microsoft.public.sqlserver.programming

1 Jul 2005 8:36 PM

RitaG

Hi.

I have a SQL 2000 table (Table1) whose 1 column (column1) I need to update.
I have to match Table1.Column1 to the column of another table
(Table2.Column1) and if a match is found I'll update Table1.Column1 with
Table2.Column2.

I have the code for that. What I need is to set Table1.Column1 to a default
value of 'AO' if no match is found. I dont know how to do this within 1
Update statement. Currently I have 2 update statements but think there has to
be a way to accomplish this within 1 Update statement.

Here's my first Update statement:
UPDATE Table1
SET Column1 = (SELECT Table2.Column2 FROM Table2
             INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)

Here's my 2nd Update Statement:
UPDATE Table1
SET Column1 = 'AO'
    WHERE Table1.Column1 <> Table2.Column1

Thanks,
Rita

1 Jul 2005 9:58 PM

Gert-Jan Strik

Try this:

UPDATE Table1
SET Column1 = COALESCE( (
SELECT Table2.Column2
FROM Table2
WHERE Table1.Column1=Table2.Column1
), 'AO')

HTH,
Gert-Jan

RitaG wrote:
Show quote

>
> Hi.
>
> I have a SQL 2000 table (Table1) whose 1 column (column1) I need to update.
> I have to match Table1.Column1 to the column of another table
> (Table2.Column1) and if a match is found I'll update Table1.Column1 with
> Table2.Column2.
>
> I have the code for that. What I need is to set Table1.Column1 to a default
> value of 'AO' if no match is found. I dont know how to do this within 1
> Update statement. Currently I have 2 update statements but think there has to
> be a way to accomplish this within 1 Update statement.
>
> Here's my first Update statement:
> UPDATE Table1
> SET Column1 = (SELECT Table2.Column2 FROM Table2
> INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)
>
> Here's my 2nd Update Statement:
> UPDATE Table1
> SET Column1 = 'AO'
> WHERE Table1.Column1 <> Table2.Column1
>
> Thanks,
> Rita

1 Jul 2005 10:14 PM

Tore

Neither of your update statements are appropriate in themselves.

In the first statement, instead of a join (which returns multiple rows as
written), use a correlated subquery. Use the COALESCE function to return an
alternative value when the first one is NULL as long as you do not want to
propagate Null values from Table2.Column2. With this approach, you don't
need a second statement.

UPDATE Table1
SET Column1 = COALESCE((SELECT Table2.Column2 FROM Table2
           WHERE Table2.Column1 = Table1.Column1), 'AO')

If Table2.Column2 may contain Nulls, and you do want Table1.Column1 to
"inherit" these Null values, the COALESCE won't give you what you need. In
that case, you need to create a selection that always returns exactly one
value, either the Table2.Column2 value (Null or non-Null), or 'AO' when not
found.

Beware of using <> in a SQL Statement! Your second query - besides being
syntactically invalid - will create a cartesian product of all non-matching
rows in Table1 and Table2:

Table1:

Column1
---------
'01'
'02'
'03'

Table2:

Column1, Column2
--------------------
'01', ' 1'
'03', NULL

With a
SELECT Table1.Column1, Table2.Column2
FROM Table1, Table2
WHERE Table1.Column1 <> Table2.Column1

would give you:

'01', NULL
'02', ' 1'
'02', NULL
'03', ' 1'

Since you are always comparings sets, the <> comparison usually results in
many matches. In most cases, a NOT IN or (better) NOT EXISTS with a
correlated subquery will provide the answer you need. But depending on what
you are trying to achieve, using an OUTER JOIN and checking for a NULL value
of the joining column or primary key in the inner table will tell you
whether there is a matching row or not. This is useful in your case.

Therefore, try the following:

UPDATE Table1
SET Column1 = (
SELECT
CASE WHEN T2.Column1 IS NULL
   THEN 'AO'
   ELSE T2.Column2
END
FROM Table1 as T1
LEFT JOIN Table2 as T2
ON T1.Column1 = T2.Column1
WHERE T1.Column1 = Table1.Column1
)

Note the join with a second instance of Table1 (T1) in the subquery, and
then correlating the subquery back to the UPDATE target table (Table1).
Also, if you are not familiar with the CASE expression in T-SQL, the most
important thing to realize is that it is not a statement but an expression
construction (there is no "action", just a resulting value).

HTH,
Tore.

Show quote

"RitaG" <Ri***@discussions.microsoft.com> wrote in message
news:32E4A9B7-4612-43A5-9B9D-6F2AF3249D30@microsoft.com...
> Hi.
>
> I have a SQL 2000 table (Table1) whose 1 column (column1) I need to
> update.
> I have to match Table1.Column1 to the column of another table
> (Table2.Column1) and if a match is found I'll update Table1.Column1 with
> Table2.Column2.
>
> I have the code for that. What I need is to set Table1.Column1 to a
> default
> value of 'AO' if no match is found. I dont know how to do this within 1
> Update statement. Currently I have 2 update statements but think there has
> to
> be a way to accomplish this within 1 Update statement.
>
> Here's my first Update statement:
> UPDATE Table1
> SET Column1 = (SELECT Table2.Column2 FROM Table2
> INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)
>
> Here's my 2nd Update Statement:
> UPDATE Table1
> SET Column1 = 'AO'
> WHERE Table1.Column1 <> Table2.Column1
>
> Thanks,
> Rita

1 Jul 2005 10:19 PM

Tore

BTW, all of these approaches assume that there is a 1-{0,1} relationship
between Table1 and Table2. I.e. if there are multiple matching rows in
Table2, you need a way to determine which one to use for the update.

HTH,
Tore.

Show quote

"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:e5$f0oofFHA.3124@TK2MSFTNGP12.phx.gbl...
> Neither of your update statements are appropriate in themselves.
>
> In the first statement, instead of a join (which returns multiple rows as
> written), use a correlated subquery. Use the COALESCE function to return
> an alternative value when the first one is NULL as long as you do not want
> to propagate Null values from Table2.Column2. With this approach, you
> don't need a second statement.
>
> UPDATE Table1
> SET Column1 = COALESCE((SELECT Table2.Column2 FROM Table2
>           WHERE Table2.Column1 = Table1.Column1), 'AO')
>
> If Table2.Column2 may contain Nulls, and you do want Table1.Column1 to
> "inherit" these Null values, the COALESCE won't give you what you need.
> In that case, you need to create a selection that always returns exactly
> one value, either the Table2.Column2 value (Null or non-Null), or 'AO'
> when not found.
>
> Beware of using <> in a SQL Statement! Your second query - besides being
> syntactically invalid - will create a cartesian product of all
> non-matching rows in Table1 and Table2:
>
> Table1:
>
> Column1
> ---------
> '01'
> '02'
> '03'
>
> Table2:
>
> Column1, Column2
> --------------------
> '01', ' 1'
> '03', NULL
>
> With a
> SELECT Table1.Column1, Table2.Column2
> FROM Table1, Table2
> WHERE Table1.Column1 <> Table2.Column1
>
> would give you:
>
> '01', NULL
> '02', ' 1'
> '02', NULL
> '03', ' 1'
>
> Since you are always comparings sets, the <> comparison usually results in
> many matches. In most cases, a NOT IN or (better) NOT EXISTS with a
> correlated subquery will provide the answer you need. But depending on
> what you are trying to achieve, using an OUTER JOIN and checking for a
> NULL value of the joining column or primary key in the inner table will
> tell you whether there is a matching row or not. This is useful in your
> case.
>
> Therefore, try the following:
>
> UPDATE Table1
> SET Column1 = (
> SELECT
> CASE WHEN T2.Column1 IS NULL
>   THEN 'AO'
>   ELSE T2.Column2
> END
> FROM Table1 as T1
> LEFT JOIN Table2 as T2
> ON T1.Column1 = T2.Column1
> WHERE T1.Column1 = Table1.Column1
> )
>
> Note the join with a second instance of Table1 (T1) in the subquery, and
> then correlating the subquery back to the UPDATE target table (Table1).
> Also, if you are not familiar with the CASE expression in T-SQL, the most
> important thing to realize is that it is not a statement but an expression
> construction (there is no "action", just a resulting value).
>
>
> HTH,
> Tore.
>
> "RitaG" <Ri***@discussions.microsoft.com> wrote in message
> news:32E4A9B7-4612-43A5-9B9D-6F2AF3249D30@microsoft.com...
>> Hi.
>>
>> I have a SQL 2000 table (Table1) whose 1 column (column1) I need to
>> update.
>> I have to match Table1.Column1 to the column of another table
>> (Table2.Column1) and if a match is found I'll update Table1.Column1 with
>> Table2.Column2.
>>
>> I have the code for that. What I need is to set Table1.Column1 to a
>> default
>> value of 'AO' if no match is found. I dont know how to do this within 1
>> Update statement. Currently I have 2 update statements but think there
>> has to
>> be a way to accomplish this within 1 Update statement.
>>
>> Here's my first Update statement:
>> UPDATE Table1
>> SET Column1 = (SELECT Table2.Column2 FROM Table2
>>           INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)
>>
>> Here's my 2nd Update Statement:
>> UPDATE Table1
>> SET Column1 = 'AO'
>> WHERE Table1.Column1 <> Table2.Column1
>>
>> Thanks,
>> Rita
>
>

1 Jul 2005 10:16 PM

Hugo Kornelis

On Fri, 1 Jul 2005 13:36:15 -0700, RitaG wrote:

Show quote

>Hi.
>
>I have a SQL 2000 table (Table1) whose 1 column (column1) I need to update.
>I have to match Table1.Column1 to the column of another table
>(Table2.Column1) and if a match is found I'll update Table1.Column1 with
>Table2.Column2.
>
>I have the code for that. What I need is to set Table1.Column1 to a default
>value of 'AO' if no match is found. I dont know how to do this within 1
>Update statement. Currently I have 2 update statements but think there has to
>be a way to accomplish this within 1 Update statement.
>
>Here's my first Update statement:
>UPDATE Table1
>SET Column1 = (SELECT Table2.Column2 FROM Table2
>              INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)
>
>Here's my 2nd Update Statement:
>UPDATE Table1
>SET Column1 = 'AO'
>    WHERE Table1.Column1 <> Table2.Column1
>
>Thanks,
>Rita

Hi Rita,

Try:

UPDATE Table1
SET Column1 = COALESCE((SELECT Table2.Column2
FROM Table2
WHERE Table2.Column1 = Table1.Column1), 'AO')
(untested)

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

5 Jul 2005 4:14 PM

RitaG

Thanks so much to all who responded to my question. I used the COALESCE
statement (didn't know about that one!) and it worked fine.

I really appreciate all the efforts taken to answer the question.

Rita.

Show quote

"RitaG" wrote:

> Hi.
>
> I have a SQL 2000 table (Table1) whose 1 column (column1) I need to update.
> I have to match Table1.Column1 to the column of another table
> (Table2.Column1) and if a match is found I'll update Table1.Column1 with
> Table2.Column2.
>
> I have the code for that. What I need is to set Table1.Column1 to a default
> value of 'AO' if no match is found. I dont know how to do this within 1
> Update statement. Currently I have 2 update statements but think there has to
> be a way to accomplish this within 1 Update statement.
>
> Here's my first Update statement:
> UPDATE Table1
> SET Column1 = (SELECT Table2.Column2 FROM Table2
>              INNER JOIN Table1 ON Table2.Column1 = Table1.Column1)
>
> Here's my 2nd Update Statement:
> UPDATE Table1
> SET Column1 = 'AO'
>     WHERE Table1.Column1 <> Table2.Column1
>
> Thanks,
> Rita