Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update statement where SET value is variable Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 3:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 20, 2015 3:39 AM
Points: 92, Visits: 395
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
Post #1392900
Posted Wednesday, December 5, 2012 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:36 AM
Points: 5,225, Visits: 5,143
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1392904
Posted Wednesday, December 5, 2012 4:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 20, 2015 3:39 AM
Points: 92, Visits: 395
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.
Post #1392907
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse