Update Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-13 : 15:08:27

Hi Guys
I’m having trouble doing update statement and was wondering if anyone could help me.
The scenario is that I have to put product info into 2 different table which combined will show information on a product which will later be shown on a website.

These tables are
Product:
ProductID, Short Description, LongDescription
Product AdditionalInfo:
ProductAdditionalInfoID, ProductID, PublicationDate, FormatDescription, URN
ProductID being the link key.

The information is first being loaded into temporary tables called:
TMP.WebDescription:
UniqueStockcCode,ShortDescription,LongDescription, Text

TMP.WebStock
UniqueStockCode, PublicationDate, FormatDescription
UniqueStockCode being the link key.

The ProductID (Product) and ProductAdditionalInfoID(ProductAdditionalInfo) are the primary keys in the two tables. Each automatically generated once a record is inserted.

Please note the two tables have been trimmed down just to show the relevant information for this question.

I’ve been given some instructions on what data has to be entered as follows:
Product:
A. Add a short description
B. Add a long description

ProductAdditionalInfo:
A. Add Product ID from Product
B. Add Publication Date
C. Add Format Description.
D. Add URN (Unique Stock Code)

Now here is where I get confused, I can get the product data in, but stuck on the ProductAdditionalInfo as I have no idea how to get the ProductID from the product table into ProductAdditionalInfo with the matching relevant data.

Anyone have any ideas on how to do this as I am completely stuck?

Thanking you in advance!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-13 : 15:36:37

Hello,
I think you have to add a column to the table Product which can hold the UniqueStockCode.

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-13 : 18:50:05

Hey

Thanks, but I can't change the table structure as it has to be set this way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 01:05:40

You can use OUTPUT clause to achieve this:-

DECLARE @INSERTED_PRODUCTS table
(
ProductID int,
Short Description varchar(100), 
LongDescription varchar(1000)
)

INSERT INTO Product (ShortDescription,LongDescription)
OUTPUT INSERTED.ProductID,INSERTED.ShortDescription, INSERTED.LongDescription INTO @INSERTED_PRODUCTS
SELECT ShortDescription,LongDescription
FROM TMP.WebDescription

INSERT INTO ProductAdditionalInfo (ProductID, PublicationDate, FormatDescription, URN) 
SELECT ip.ProductID,ws.PublicationDate,
ws.FormatDescription,ws.UniqueStockCode
FROM @INSERTED_PRODUCTS ip
INNER JOIN TMP.WebDescription wd
ON wd.ShortDescription=ip.ShortDescription
AND wd.LongDescription=ip.LongDescription
INNER JOIN TMP.WebStock ws
ON ws.UniqueStockCode=wd.UniqueStockcCode

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-14 : 02:05:36

Hi

Thanks for that, Is there a way to do it without the output clause?

sorry to be a pain!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 02:20:39

quote:
Originally posted by rcr69er

Hi

Thanks for that, Is there a way to do it without the output clause?

sorry to be a pain!!!

Why ? what's wrong with using OUTPUT ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 03:44:31

The only reasons are

1) He is not using Microsoft SQL Server
2) He is not using Microsoft SQL Server 2005 or later
3) He is not familiar with the OUTPUT operator

E 12°55'05.25"
N 56°04'39.16"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-14 : 03:47:13

Hey

Number 3 is spot on :D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 03:49:30

http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 03:52:15

1) unlikely with 173 posts so far
2) should be as this is Transact-SQL (2005) forum or unless OP posted in the wrong forum
3) maybe . . . but then you can always refer to the BOL

So no excuse

[edit]Damn ! took too long to write some garbage[/edit]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 05:32:39

quote:
Originally posted by rcr69er

Hey

Number 3 is spot on :D

Use it and learn it

. its always by trying that you learn new things.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources