assigning values to fields

hey all,
let's say i have the following records:
Name, Inv#, Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, 1, Desc1
Cust1, 2, Desc1
Cust1, 2, Desc2
How would you make those null values 3's or the MAX(Inv#) for Cust1?
thanks,
rodcharIf this is your entire table, you might need to clean it up to include a
key. Otherwise,
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# ) + 1
FROM tbl t1 )
WHERE Inv# IS NULL ;
Anith|||Try:
SELECT NAME, ISNULL(INV,3), DESCCOL
FROM YOURTABLE
OR
SELECT NAME, ISNULL(INV,(SELECT MAX(INV)FROM YOURTABLE)), DESCCOL
FROM YOURTABLE
OR
DECLARE @.VAL INT
SELECT @.VAL = MAX(INV) FROM YOURTABLE
SELECT NAME, ISNULL(INV,@.VAL), DESCCOL
FROM YOURTABLE
HTH
Jerry
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||UPDATE YourTable
SET [Inv#] =
(
SELECT MAX([Inv#])
FROM YourTable Y1
WHERE Y1.Name = YourTable.Name
)
WHERE YourTable.[Inv#] IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:34412E99-897D-42DE-AB25-25C689902ABA@.microsoft.com...
> hey all,
> let's say i have the following records:
> Name, Inv#, Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, 1, Desc1
> Cust1, 2, Desc1
> Cust1, 2, Desc2
> How would you make those null values 3's or the MAX(Inv#) for Cust1?
> thanks,
> rodchar
>|||3 things for everyone:
1st: Thanks for the great replies
2nd:
UPDATE Transactions
SET InvNo = ( SELECT MAX( InvNo ) + 1
FROM Transactions t1 )
WHERE InvNo IS NULL ;
This worked for me but what i thought would happen with this is that once it
updated the first null and made it a 3 the 2nd null would become a 4. Can
anyone explain please?
3rd:
This same query didn't work in an access database it said that this wasn't
an updateable query. any ideas?
thanks again,
rodchar
"Anith Sen" wrote:

> If this is your entire table, you might need to clean it up to include a
> key. Otherwise,
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# ) + 1
> FROM tbl t1 )
> WHERE Inv# IS NULL ;
> --
> Anith
>
>|||>> This worked for me but what i thought would happen with this is that once
I am not sure what to explain, since the code is simple and clear. In case,
you are looking for sequentially incrementing value to replace the NULLs,
you'd have to use a "ranking" mechanism like:
UPDATE tbl
SET Inv# = ( SELECT MAX( Inv# )
FROM tbl t1 ) +
( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Name = tbl.Name
AND t1.Descr <= tbl.Descr
AND t1.Inv# IS NULL )
FROM tbl
WHERE Inv# IS NULL ;
Depending on the ranking variations you need, you'll have to adjust the
correlations in the subquery using COUNT(*).
Access has different updateability rules and different UDPATE dialect than
SQL Server. If you are using MS Access, consider posting this question in an
Access forum.
Anith|||i'm sorry for not being clear. your code posting is exactly what i needed. i
was just curious why it didn't make the nulls sequential. Because once it
makes the first null record a 3 wouldn't that be the new MAX(InvNo), and in
turn making the last null value a 4.
just trying to understand how the engine thinks and works. this helped a lot
.
"Anith Sen" wrote:

> I am not sure what to explain, since the code is simple and clear. In case
,
> you are looking for sequentially incrementing value to replace the NULLs,
> you'd have to use a "ranking" mechanism like:
> UPDATE tbl
> SET Inv# = ( SELECT MAX( Inv# )
> FROM tbl t1 ) +
> ( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Name = tbl.Name
> AND t1.Descr <= tbl.Descr
> AND t1.Inv# IS NULL )
> FROM tbl
> WHERE Inv# IS NULL ;
> Depending on the ranking variations you need, you'll have to adjust the
> correlations in the subquery using COUNT(*).
>
> Access has different updateability rules and different UDPATE dialect than
> SQL Server. If you are using MS Access, consider posting this question in
an
> Access forum.
> --
> Anith
>
>|||>> i was just curious why it didn't make the nulls sequential.
With no correlation, the value is generated only once for the entire
dataset. With a correlation, the values are generated for each matching row
in the dataset.
No problem.
Anith|||thank you everyone for the help. this has been very productive.
"Anith Sen" wrote:

> With no correlation, the value is generated only once for the entire
> dataset. With a correlation, the values are generated for each matching ro
w
> in the dataset.
>
> No problem.
> --
> Anith
>
>|||On Fri, 14 Oct 2005 10:09:04 -0700, ari wrote:

>2nd:
>UPDATE Transactions
>SET InvNo = ( SELECT MAX( InvNo ) + 1
>FROM Transactions t1 )
>WHERE InvNo IS NULL ;
>This worked for me but what i thought would happen with this is that once i
t
>updated the first null and made it a 3 the 2nd null would become a 4. Can
>anyone explain please?
Hi ari,
In SQL, all operations are done "at once". At least in theory. In
practice, they will eventuelly, somewhere deep in the engine, be
processed one row at a time, but the DB should behave as if the complete
statement is executed at once.
That's why you can swap columns without temp storage to hold the old
value, like you would in procedural languages:
UPDATE SomeTable
SET A = B,
B = A
WHERE ...
The right-hand B and A both refer to the "old" values (before the
update). The DB can process this internally ion any order it wants, as
long as the result looks as if it was all executed at once.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Assigning extra information to all database table fields

I'm an experienced desktop app programmer, but fairly new to database programming. I'm working with C#/SQL right now. I understand the concept of a lookup table, as in storing a two-character state (such as CA) in a field in one table, and then being able to get the full state name (such as California) from a second table:

Table: Address
AddressID int <PK>
StateCode char(2) <FK>
...

Table: State
StateCode char(2) <PK>
StateName varchar(25)

My question: is there some similar way to be able to provide just about every field of every table with a means of holding common, extra information? For instance, let's say I wanted to store, say, an extended name and some special code with every field in three tables. As in this simplistic example:

Table: A
Field1 int
Field2 varchar(20)

Table: B
Field1 bit
Field2 int

Table: C
Field1 int

Table: ExtraInfo
LongName varchar(50)
TypeCode int

If I wanted to be able to have the information in the ExtraInfo table associated with each field in Table A, Table B and Table C, how would I do that?

Thanks!

I'm not sure that I understand exactly what you are trying to do.

Might you want a table "ExtraInfo" with columns

Table_Name varchar(50),

Column_Name varchar(50),

Extra_Info varchar(50),

TypeCode int

Maybe Extra_Info could be something like a column description?

Is that the right idea?

Dan

|||I'm not totally clear about the concept. Perhaps looking in Books Online for 'Extended Properties' would prove to be helpful.|||

Just to add my 3 cents worth to the interrogation, are you wanting to store metadata? Or are you trying to store information for use by other users? That would make a big difference.

Also, if you are trying to be generic, like having a base class, it is seldom a good idea, even if it seems like a good idea at this point. SQL is centered around implementing a specific design, not genericness (genericism, genericisticy?)

|||

Louis -

Yes, metadata. No user-entered information.

And no, I'm not looking to be generic.

The information would be very specific. As in, say, being able to provide every field of any (or all) tables with a string title (that differs from the field's column name). Or any other bit of information that might be useful for all fields. For the sake of a solution, ignore the exact type of information (metadata) that's to be tracked. It could be anything. Let's say I want to assign one of three colors (red, yellow or green) to every field of every table. The data can be anything. I just want to know how I could store some common, non-generic data to every field. To, in a sense, make every field of any table a structure (to put it in app programming terms). It seems like there should be some standard way of doing this. It's probably my desktop app programming background limiting my explanation.

Thanks!

|||

DanR1 -

I think this might be a solution. I'll look into it. If the one table had TableName and ColumnName fields as you suggest, then it could have any other fields for the metadata, and the TableName and ColumnName fields provide a way back to each particular field of any table. That might work. I'm not sure if that's a "legitimate" solution as far as database design goes (as mentioned, I'm not a big DB programmer), but it is certainly on the right track for what I'm trying to do.

Thanks

|||

Arnie -

I've googled, searched Safari Online, everything. Nothing.

From a programming perspective (which I think in terms of), this is easy. For instance, in OOP there could be a base class Animal that keeps track of an animal's color. Then, any classes of particular animals could inherit the Animal class (and thus each animal could hold color information) as well as keep track of information particular to that animal:

class Animal
{
int color;
}

class Dog : Animal
{
bool chasesCars;
}

class Pig : Animal
{
bool likesMud;
}

The above, in DB terms, would be a table Dog with one bit field and a table Pig that also had a bit field. Besides that one piece of information though, each of these fields could also keep track of a color. That's the kind of "extra" information I'm trying to tie to each field.

Thanks

|||

It sounds like you just need to add additional columns to the tables. Such as [DescriptiveTitle], [DefaultValue], [MyMetaData], etc.

It is very common for additional metadata columns to be added to tables. For example, in databases I create, all tables have columns [InsertedBy], InsertedDate], [ChangeBy], [ChangeDate]. These columns are rarely used by applications, but are for administrative and auditing purposes. You have different needs, but the solution is similar.

Creating a 'metadata' table seems overly cumbersome and frought with peril.

|||

Dan Parks,

I'd have to agree with Arnie that it sounds like the database solution for what you are wanting to do would be to add columns to the table, and allow them to have NULL entries for cases where you did not set a value (such as the COLOR of your DOG or PIG).

Although it would be possible to use the Binary column type (I don't remember the exact name for it, but it is what allows JPG, BMP, etc., data to be stored in a table -- almost always by storing a LINK to the place where the database actually stores the large binary data values -- almost never in the row of data in the table) and store therein a Structure such as you might use in C++, you would then have to write your own procedures to unpack that structure if you want to search it for certain values, or if you want to change certain values. That seems to be avoiding many of the benefits of having a database with your ability to add columns as needed, and to JOIN tables on columns, and search for values in columns, etc.

Before I became involved with SQL and databases most of my programming had been in FORTRAN. Our data were stored in sorted fixed-record-length data files. We would have to perform binary searches to find the records we needed to perform a computation. We would have to create our own accumulator variables to sum quantities. If a coworker wanted to find all entries wherein a data column had a certain value, we would have to write a special program to search the entire file for that value, and display the records where it was found. If he wanted to search for multiple potential values in that column, we would have to write the program to allow for multiple entries. Now it is so much easier:

select *

from TABLE

where COLUMN1 in (value1, value2, value3)

order by 1,2,3,4,5

SQL has made much of my earlier programming rather trivial in terms of the SQL necessary to perform the same tasks. And because of the simpler SQL code (simpler to write, simpler to maintain) we were able easily to improve the computations to deal with nuances in the data that seemed awfully ugly in the FORTRAN solution. (Imagine a complex WHERE clause, relying on a number of subqueries of the same data -- easy in SQL, rather ugly in FORTRAN.)

Dan