CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Irvin Dua
Irvin Dua

Reputation: 771

Get value from other foreign key table based on current table value

I am working on a project where I need to extract the data from excel sheet to SQL Server , well that bit have done successfully. Now my problem is that for a particular column
called product size, I want to update current table based on product size in other table, I am really very confused , please help me out

Please find the table structure 

    CREATE TABLE [dbo].[T_Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[PartNo] [nvarchar](255) NULL,
[CategoryID] [int] NULL,
[MaterialID] [float] NULL,
[WireformID] [float] NULL,
[ProductName] [nvarchar](50) NULL,
[ProductSize] [nvarchar](50) NULL,
[ProductLength] [varchar](20) NULL,
[ProductActive] [bit] NULL,
[ProductImage] [varchar](60) NULL
 ) ON [PRIMARY]

     CREATE TABLE [dbo].[T_ProductSize](
[Code] [int] IDENTITY(1,1) NOT NULL,
[ProductSize] [nvarchar](50) NULL,
[Length] [nchar](20) NULL
 ) ON [PRIMARY]

 GO

Upvotes: 0

Views: 2772

Answers (3)

Dimith
Dimith

Reputation: 379

Try this...

UPDATE  t2
SET     t2.ProductLength = t1.Length
FROM    dbo.T_ProductSize t1
        INNER JOIN dbo.T_Product t2 ON t1.ProductSize = t2.ProductSize

Upvotes: 0

Steve Pettifer
Steve Pettifer

Reputation: 2043

OK, so ignore the previous answer, I got the wrong end of the stick!!

You want something like this I think:

UPDATE T_Product
SET [ProductLength] = ps.[Length]
FROM T_Product p
INNER JOIN T_ProductSize ps
ON p.[ProductSize] = ps.[ProductSize]

That will take the Length value from T_ProductSize and place it in T_Product.ProductLength based on the value of T_Product.ProductSize

Upvotes: 2

Steve Pettifer
Steve Pettifer

Reputation: 2043

You mention a foreign key but you haven't included a definition for it. Is it between the two tables in your example? If so, which columns make the key? Is product size the key? If so, then your question doesn't make a lot of sense as the value will be the same in both tables.

Is it possible that you mean the product size is to be stored in a separate table and not in the T_Product table? In that case then instead of ProductSize in T_Product you will want the code from the T_ProductSize table (can I also suggest that instead of 'code' you call it 'ProductSizeCode' or better yet 'ProductSizeId' or similar? having columns simply called code can be very confusing as you have no simple way of know what table that value is in). Also, you should always create a primary key on each table: You cannot have a foreign key without one. They don't have to be clustered, that will depend upon hwo your search the table, but I am using a clustered PK for this example. That would give you something like this:

CREATE TABLE [dbo].[T_Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [PartNo] [nvarchar](255) NULL,
    [CategoryID] [int] NULL,
    [MaterialID] [float] NULL,
    [WireformID] [float] NULL,
    [ProductName] [nvarchar](50) NULL,
    [ProductSizeId] [int] NOT NULL,
    [ProductLength] [varchar](20) NULL,
    [ProductActive] [bit] NULL,
    [ProductImage] [varchar](60) NULL

CONSTRAINT [PK_T_Product] PRIMARY KEY CLUSTERED 
    (
    [ProductID] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[T_ProductSize](
    [ProductSizeId] [int] IDENTITY(1,1) NOT NULL,
    [ProductSize] [nvarchar](50) NULL,
    [Length] [nchar](20) NULL
CONSTRAINT [PK_T_ProductSize] PRIMARY KEY CLUSTERED 
    (
    [ProductSizeId] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

--Now add your foreign key to T_Product.
ALTER TABLE [T_Product]  WITH NOCHECK ADD CONSTRAINT [FK_Product_ProductSize] FOREIGN KEY([ProductSizeId])
REFERENCES [T_ProductSize] ([ProductSizeId])
GO
ALTER TABLE [T_Product] CHECK CONSTRAINT [FK_Product_ProductSize]
GO

Now, to retrieve your product along with the product size use something like this:

SELECT p.[ProductID], p.[PartNo], p.[CategoryID], p.[MaterialID], p.[WireformID], p.[ProductName],
    ps.[ProductSize], ps.[Length], p.[ProductLength], p.[ProductActive], p.[ProductImage]
FROM [T_Product] p
INNER JOIN [T_ProductSize] ps
ON ps.[ProductSizeId] = p.[ProductSizeId]

If I have understood you correctly, then this is what I think you're after. If not, then have another go at explaining what it is you need and I'll try again.

Upvotes: 0

Related Questions