StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs one of my DB-fields NOT NULL?
text
Body
copied!<p>I have to deal with a table where there is a set of fields each followed by a second field that will hold a suggested new value until this change is confirmed.</p> <p>It looks a little like this:</p> <pre><code>refID field1 newField1 field2 newField2 ... </code></pre> <p>refID is an ID value that links to a master table. One row in the master table can have n rows in my detail table. The data-types include ints, strings and dateTimes.</p> <p>Now, i'm looking to have a query that tells me, given a refID, if there are any suggested changes in the detail table.</p> <p>I was playing around a little with some UNION selects, with COALESCE() and ISNULL() ... but all those attempts looked a little weird at best. The DB is MS-SQL-Server 2005.</p> <p>For clarification of my problem:</p> <pre><code>--this is a simplification of the details table in question CREATE TABLE [dbo].[TEST_TABLE]( [ID] [int] IDENTITY(1,1) NOT NULL, [refID] [int] NOT NULL, [firstName] [varchar](50) NULL, [newFirstName] [varchar](50) NULL, [lastName] [varchar](50) NULL, [newLastName] [varchar](50) NULL ) --here we insert a detail row ... one of many that might exist for the master table (e.g. data about the company) insert into TEST_TABLE(refID, firstName, lastName) values(666, 'Bill', 'Ballmer') --this is what happens when a user saves a suggested change update TEST_TABLE SET newLastName = 'Gates' where ID = 1 --and this is what happens when this suggestion is accepted by a second user update TEST_TABLE set lastName=newLastName, newLastName = NULL where ID = 1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload