Need Help With SQL Statement

[jbow0527]

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.

[Serge Rielau]

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

[Bob Stearns]

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.

[Knut Stolze]

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

[Serge Rielau]

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