Re: Update SQL Problem

From: Keith Lawrence <false_at_no.spam>
Date: 1998/02/06
Message-ID: <6bg7rk$l1k$1@news.u-net.com>#1/1

Mark Buffington wrote in message <01bd3349$9b7c84a0$54602e9c_at_PC5312.excel.net>...
>I have problem where I want to update the contents of one field with the
>contents of another field in another table.
>
>Table A has part and description.
>Table B has part and description plus many other fields.
>
>I want to update B's description with the description from A where all the
>parts are equal.
>
>Can this be done using straight SQL?

Short answer is yes, try...
UPDATE B SET Description = (SELECT Description FROM A WHERE A.Part = B.Part);

But why bother with all that data duplication? I assume it's because you want a... SELECT * FROM B;
to show the description.

Much better is to remove the Description field from B and publish a VIEW of B thus - CREATE OR REPLACE VIEW vB AS
SELECT A.Description, B.* FROM A,B WHERE A.Part = B.Part;

Instead of SELECTing from the base A table a simple... SELECT * FROM vB;
will give you the description without all the data duplication.

Hope that helps

Keith L Received on Fri Feb 06 1998 - 00:00:00 CST