Here is what I have:

Table1 with two key fields: PartNum and WH# (warehouse number)
Table2 with only one key field: PartNum

Both tables contain a field called Price.

I want to update Table1 with the Price field from Table2 where the
PartNum field matches. Of course the WH# field does not have to match
because Table2 does not contain that field.

What SQL statement would I use to accomplish this? Any help would be
appreciated.

jbow0527 wrote:
> Here is what I have:
>
> Table1 with two key fields: PartNum and WH# (warehouse number)
> Table2 with only one key field: PartNum
>
> Both tables contain a field called Price.
>
> I want to update Table1 with the Price field from Table2 where the
> PartNum field matches. Of course the WH# field does not have to match
> because Table2 does not contain that field.
>
> What SQL statement would I use to accomplish this? Any help would be
> appreciated.
>
UPDATE Table1 SET price = (SELECT price FROM Table2 WHERE Table1.PartNum
= Table2.PartNum) WHERE EXISTS(SELECT 1 FROM Table2 WHERE Table1.PartNum
= Table2.PartNum);

Cheers
Serge

Serge Rielau wrote:
> jbow0527 wrote:
>
>> Here is what I have:
>>
>> Table1 with two key fields: PartNum and WH# (warehouse number)
>> Table2 with only one key field: PartNum
>>
>> Both tables contain a field called Price.
>>
>> I want to update Table1 with the Price field from Table2 where the
>> PartNum field matches. Of course the WH# field does not have to match
>> because Table2 does not contain that field.
>>
>> What SQL statement would I use to accomplish this? Any help would be
>> appreciated.
>>
> UPDATE Table1 SET price = (SELECT price FROM Table2 WHERE Table1.PartNum
> = Table2.PartNum) WHERE EXISTS(SELECT 1 FROM Table2 WHERE Table1.PartNum
> = Table2.PartNum);
>
> Cheers
> Serge
Wouldn't it be simpler (in DB2 UDB v8.1.2 and later) to:

merge into table1
using table2
on table1.partnum=table2.partnum
when matched then
update set price = table2.price

I realize your solution is more portable (and that OP didn't specify his
version), but for me at least, takes much more "unraveling" to
understand. As far as I know, your solution is what the merge statement
becomes at some point during sql processing or optimization.

Bob Stearns wrote:

> Serge Rielau wrote:
>> jbow0527 wrote:
>>
>>> Here is what I have:
>>>
>>> Table1 with two key fields: PartNum and WH# (warehouse number)
>>> Table2 with only one key field: PartNum
>>>
>>> Both tables contain a field called Price.
>>>
>>> I want to update Table1 with the Price field from Table2 where the
>>> PartNum field matches. Of course the WH# field does not have to match
>>> because Table2 does not contain that field.
>>>
>>> What SQL statement would I use to accomplish this? Any help would be
>>> appreciated.
>>>
>> UPDATE Table1 SET price = (SELECT price FROM Table2 WHERE Table1.PartNum
>> = Table2.PartNum) WHERE EXISTS(SELECT 1 FROM Table2 WHERE Table1.PartNum
>> = Table2.PartNum);
>
> Wouldn't it be simpler (in DB2 UDB v8.1.2 and later) to:
>
> merge into table1
> using table2
> on table1.partnum=table2.partnum
> when matched then
> update set price = table2.price
>
> I realize your solution is more portable (and that OP didn't specify his
> version), but for me at least, takes much more "unraveling" to
> understand. As far as I know, your solution is what the merge statement
> becomes at some point during sql processing or optimization.

Serge's version would be much clearer to me because it says what the
statement is doing: only an UPDATE - not a MERGE. But I guess that's very
much influenced by personal taste.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Bob Stearns wrote:
> Serge Rielau wrote:
>
>> jbow0527 wrote:
>>
>>> Here is what I have:
>>>
>>> Table1 with two key fields: PartNum and WH# (warehouse number)
>>> Table2 with only one key field: PartNum
>>>
>>> Both tables contain a field called Price.
>>>
>>> I want to update Table1 with the Price field from Table2 where the
>>> PartNum field matches. Of course the WH# field does not have to match
>>> because Table2 does not contain that field.
>>>
>>> What SQL statement would I use to accomplish this? Any help would be
>>> appreciated.
>>>
>> UPDATE Table1 SET price = (SELECT price FROM Table2 WHERE
>> Table1.PartNum = Table2.PartNum) WHERE EXISTS(SELECT 1 FROM Table2
>> WHERE Table1.PartNum = Table2.PartNum);
>>
>> Cheers
>> Serge
>
> Wouldn't it be simpler (in DB2 UDB v8.1.2 and later) to:
>
> merge into table1
> using table2
> on table1.partnum=table2.partnum
> when matched then
> update set price = table2.price
>
> I realize your solution is more portable (and that OP didn't specify his
> version), but for me at least, takes much more "unraveling" to
> understand. As far as I know, your solution is what the merge statement
> becomes at some point during sql processing or optimization.
Yes, MERGE statement is the SQL2003 way and Db2 V8.1.2 way of writing this.
The UPDATE turns into something close to the MERGE rather than the other
way around.
As long as table2.partnum is unique DB2 will merge the EXIST and the
scalar subselect to generate (internally) what IDS and SQL Server users
know as:
UPDATE table1 SET price = table2.price
FROM table1, table2 WHERE table1.partnum = table2.partnum

Cheers
Serge