update statement where SET value is variable
-
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
-
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 JSET JTypeID = JType.JTypeIDFROMJINNER JOINDONJ.DID = D.DIDINNER JOINJTypeOND.CID = JType.CIDSELECT * FROM J -
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply