December 5, 2012 at 3:51 am
PROBLEM: Table J.JTypeID is full of NULLs.
I would like to run a statement to update J.JTypeID with the relevant Jtype.JTypeID per Jtype.CID where IsDefaultDeviceJState is the row selected per CID.
-----------------------------------------------------
CREATE TABLE [dbo].[J](
[JID] [int] IDENTITY(1,1)
[JTypeID] [int]
[DID] [int]
CREATE TABLE [dbo].[JType](
[JTypeID] [int] IDENTITY(1,1)
[CID] [int]
[IsDefaultDeviceJState] [bit]
CREATE TABLE [dbo].[D](
[CID] [int]
[DID] [int] IDENTITY(1,1)
-----------------------------------------------------
Sample data:
tbl.J
[JID] 1,2,3
[JTypeID] NULL,NULL,NULL
[DID] 1,2,1
tbl.JType
[JTypeID] ,1,2,3,4,5,6
[CID] 1,1,1,2,2,2
[IsDefaultDeviceJState] 1,0,0,0,0,0,1
tbl.D
[CID] 1,2
[DID] 1,2
-----------------------------------------------------
Basics probably but I keep getting in a muddle on my test data. Thank you for any help.
Scott
December 5, 2012 at 3:56 am
CREATE TABLE [dbo].[J]([JID] [int],[JTypeID] [int],[DID] [int])
CREATE TABLE [dbo].[JType]([JTypeID] [int],[CID] [int],[IsDefaultDeviceJState] [bit])
CREATE TABLE [dbo].[D]([CID] [int],[DID] [int])
INSERT INTO J VALUES (1,NULL,1),(2,NULL,2),(3,NULL,1)
INSERT INTO JType VALUES (1,1,1),(2,1,0),(3,1,0),(4,2,0),(5,2,0),(6,2,1)
INSERT INTO D VALUES (1,1),(2,2)
UPDATE J
SET JTypeID = JType.JTypeID
FROM
J
INNER JOIN
D
ON
J.DID = D.DID
INNER JOIN
JType
ON
D.CID = JType.CID
SELECT * FROM J
December 5, 2012 at 4:07 am
Thank you for the reply and the reformatting.
I was close then with this attempt before posting 🙂
update J
set J.JTypeID = Jtype.JTypeID
from jtype
inner join J on J.DID = D.DID
inner join D on D.CID = JType.CID
It was the SET line that was wrong but dont understand why exactly.
Tagged on "where JType.IsDefaultDeviceJState = 1" onto the end and we have a winner.
Thank you very much , great help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply