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: Wednesday, April 30, 2014 8:51 AM
Points: 92, Visits: 393
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: 2 days ago @ 4:10 AM
Points: 5,221, Visits: 5,119
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: Wednesday, April 30, 2014 8:51 AM
Points: 92, Visits: 393
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
Posted Thursday, December 6, 2012 10:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 1,945, Visits: 3,122
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you have no clue). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

None of these thing are tables! They have no keys and cannot ever have a key because of the NULL-able columns. IDENTITY is not a key; it is physical count of the physical storage attempts on one table on one machine. Are you tryignto build a fake pointer chain?

There is no such thing as a “type_id” in RDBMS. An attribute can be a “<something>_type” or “<something>_id” but not this absurd hybrid. Please read any book on basic data modeling or the ISO-11179 rules.

The “_type” is called an attribute property. It is a particular kind of attribute that uses a nominal scale. Having that bit flag in a table of types is absurd; If I have a table of blood types, what is the default blood type? An entity has a default, not a scale.

The heuristic for scales with enumerated values is
1) if the list is short and static, put it in a “CHECK x IN (..))” constraint
2) if the list is long or dynamic, use a REFERENCES clause.

What kind of entity is a “J”? What kind of entity is a “D”? Maybe D is the Devices in that flag's name? Your posting was so abbrevated it was unreadable.

Please Google the problems with UPDATE.. FROM..; it does not work! This is why Hugo Cornelius, and other MVPs want it deprecated in favor of MERGE statements. Google Hugo's blog on this topic; it covers all the errors.

Nobody uses BIT flags in RDBMS; that was assembly language. Please read http://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/.

We use declarative programming that is always correct without the need for constant updating. Here is my guess. Assign a priority to each device, ordering them from zero to n. The lowest priority number is the current default.

CREATE TABLE Junk
(junk_id INTEGER NOT NULL,
device_id INTEGER NOT NULL
REFERENCES Devices (device_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (junk_id, device_id),
device_priority SMALLINT DEFAULT 0 NOT NULL);

Now use this view to get the default devices for each piece of junk.

CREATE VIEW Default_Junk
AS
SELECT junk_id, device_id,
MIN(device_priority) AS default_device_priority
FROM Junk
GROUP BY junk_id

Every now and then, you might want to re-set or re-order the priority numbers.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1393636
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse