update using values from another table  
Author Message
SteveWaterman





PostPosted: Tue Jul 26 09:03:55 CDT 2005 Top

SQL Server Developer >> update using values from another table

I have 2 tables, for simplicity I am displaying only few fields

Article
-----------
id
title
layoutID
type


layout
--------
id
type

I just added a 'type' field to the article table and I would like to update
it with the type in the layout table based on layout ID. something like this
-
update article set article.type = layout.type where article.layoutID =
layout.id

How can I do this?

SQL Server284  
 
 
Madhivanan





PostPosted: Tue Jul 26 09:03:55 CDT 2005 Top

SQL Server Developer >> update using values from another table
Update A set A.type=L.type from Article A inner join Layout L on
A.id=L.id

Madhivanan

 
 
--CELKO--





PostPosted: Tue Jul 26 15:37:45 CDT 2005 Top

SQL Server Developer >> update using values from another table Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Have you ever considered getting a book on data modeling? The first
principle is that a data element has one and only one name in a schema.
Then we have ISO-11179 rules to follow.

>> I just added a 'type' field [sic] to the article table and I would like to update it with the type in the layout table based on layout ID. something like this <<

Columns are not fields --HUGE differences! Why do you wish to destroy
your data model? If this vague and poorly named "type" is an attribute
of a layout, then it belongs in the the Layouts table. This is closer
to what you should be doing,

CREATE TABLE Articles -- I assume youhave more than one?
(article_id INTEGER NOT NULL PRIMARY KEY,
article_title VARCHAR (30) NOT NULL,
layout_nbr INTEGER NOT NULL
REFERNCES Layouts(layout_nbr)
ON UPDATE CASCADE);

CREATE TABLE Layouts
(layout_nbr INTEGER NOT NULL PRIMARY KEY,
layout_type CHAR(5) NOT NULL
CHECK (article_type IN ('xxx', 'PG-13' ..)),
etc.);

Now you do not need to do the update. You have a relational database
design that will handle this for you, unlike the redundancy you were
adding.

For future reference, the correct syntax for getting values from one
table into another is:

UPDATE Foo
SET foo_type
= (SELECT foo_type --scalar subquery expression
FROM Bar
WHERE bar_key = foo_key)
WHERE ...;

You would of course NEVER use the proprietary UPDATE.. FROM.. syntax
because you know that the results can be unpredictable, and are always
not portable.

 
 
Hugo





PostPosted: Tue Jul 26 15:44:27 CDT 2005 Top

SQL Server Developer >> update using values from another table

>I have 2 tables, for simplicity I am displaying only few fields
>
>Article
>-----------
>id
>title
>layoutID
>type
>
>
>layout
>--------
>id
>type
>
>I just added a 'type' field to the article table and I would like to update
>it with the type in the layout table based on layout ID. something like this
>-
>update article set article.type = layout.type where article.layoutID =
>layout.id
>
>How can I do this?

Hi Mike,

Here's the best way:

ALTER TABLE Article
DROP COLUMN type
go
CREATE VIEW ArticleWithType
AS
SELECT a.id, a.title, a.layoutID, l.type
FROM Article AS a
INNER JOIN layout AS l
ON l.id = a.layoutID
go

Best, Hugo
--

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