InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 20:54:01 GMT20http://www.sqlservercentral.com/Forums/Topic1392900-391-1.aspxPlease 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 is1) if the list is short and static, put it in a “CHECK x IN (..))” constraint2) 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_JunkASSELECT junk_id, device_id, MIN(device_priority) AS default_device_priority FROM Junk GROUP BY junk_idEvery now and then, you might want to re-set or re-order the priority numbers. Thu, 06 Dec 2012 10:13:52 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1392900-391-1.aspxThank you for the reply and the reformatting.I was close then with this attempt before posting :-)update Jset J.JTypeID = Jtype.JTypeIDfrom jtypeinner join J on J.DID = D.DIDinner join D on D.CID = JType.CIDIt 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. Wed, 05 Dec 2012 04:07:31 GMTscott_lotushttp://www.sqlservercentral.com/Forums/Topic1392900-391-1.aspx[code="sql"]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.JTypeIDFROM JINNER JOIN D ON J.DID = D.DIDINNER JOIN JType ON D.CID = JType.CID SELECT * FROM J[/code]Wed, 05 Dec 2012 03:56:19 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392900-391-1.aspxPROBLEM: 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,1tbl.JType [JTypeID] ,1,2,3,4,5,6 [CID] 1,1,1,2,2,2 [IsDefaultDeviceJState] 1,0,0,0,0,0,1tbl.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.ScottWed, 05 Dec 2012 03:51:30 GMTscott_lotus