|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT, INSERT, UPDATEI'm buiding a series of SQL statement (SELECT, UPDATE and INSERT) comparing table.... e.g.: UPDATE ztemp SET tables=z.tables, colors=z.colors FROM z as z INNER join ztemp as zt ON z.ID = zt.ID (ID, tables, colors are column) ID is primary key... But I have found that some tables have more 1 Primary Key, for istance in the example above also tables is a Primary key. My question is....: the statment written above must be update as follow? UPDATE ztemp SET tables=z.tables, colors=z.colors FROM z as z INNER join ztemp as zt ON z.ID = zt.ID AND z.tables=zt.tables > My question is....: You have only one primary key but the key is a composite key consisting of > the statment written above must be update as follow? > > UPDATE ztemp SET tables=z.tables, colors=z.colors > FROM z as z > INNER join ztemp as zt > ON z.ID = zt.ID > AND > z.tables=zt.tables > multiple columns. You'll need to specify all primary key columns to uniquely identify a specific row. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "erncelen" <ernce***@discussions.microsoft.com> wrote in message news:76261FEC-79D9-4DA9-96D2-AE72111290C3@microsoft.com... >A douby that arise.... > > I'm buiding a series of SQL statement (SELECT, UPDATE and INSERT) > comparing > table.... > e.g.: > UPDATE ztemp SET tables=z.tables, colors=z.colors > FROM z as z > INNER join ztemp as zt > ON z.ID = zt.ID > > (ID, tables, colors are column) > ID is primary key... > > But I have found that some tables have more 1 Primary Key, for istance in > the example above also tables is a Primary key. > > My question is....: > the statment written above must be update as follow? > > UPDATE ztemp SET tables=z.tables, colors=z.colors > FROM z as z > INNER join ztemp as zt > ON z.ID = zt.ID > AND > z.tables=zt.tables > > >> But I have found that some tables have more than one Primary Key, for instance in the example above also "tables" is a Primary key.I think that you probably do not know what a key is or how to name data elements. A key is a subset of attribute whose values make an entity unique. It can be more than one column. You make one of the candidates keys into the PRIMARY KEY and you can keep the other keys unique by using a UNIQUE constraint. There is one and only one PRIMARY KEY per table in SQL. There are a lot of "ID-iots" that think they have a key if they put a "magical, all purpose" ID column on a table by using IDENTITY. This is an exposed physical locator based on the internal state of the machine and has nothing whatsoever to do with the data model. It is not an atribute of any entity. It is an attempt to mimic a sequential file's record numbering in an RDBMS. Also, why don't you learn the Standard syntax for UPDATEs so your code will be predictable and portable? That silly FROM clause has all kinds of problems. Start using the AS operator instead of = . Based on your recent postings, you have not bothered to read anything on SQL or RDBMS. Color of What?? Tables?? Tables are things, not attributes! You are copying needlessly proprietary syntax. UPDATE Ztemps SET floob_color = (SELECT floob_color FROM Foobar AS Z WHERE Z.table_name = Ztemps.table_name); A newsgroup is a bad place to learn the fundamentals, so I suggest that you get a few books before you post anything again. I think...
first of all you have not understand anything about I asked, may be You are learning reading and... A newsgroup is a bad place to learn reading, so I suggest that you get a few books before you post anything again or go back to school. I think that sometimes everybody has need help.... I think is normal to help and answer in a good manner.... anybody is a teacher in the life.... If you fill to answer in this way I think you should not answer any more..... Thank a lot..... Show quote "--CELKO--" wrote: > >> But I have found that some tables have more than one Primary Key, for instance in > the example above also "tables" is a Primary key. > > I think that you probably do not know what a key is or how to name data > elements. > > A key is a subset of attribute whose values make an entity unique. It > can be more than one column. > > You make one of the candidates keys into the PRIMARY KEY and you can > keep the other keys unique by using a UNIQUE constraint. There is one > and only one PRIMARY KEY per table in SQL. > > There are a lot of "ID-iots" that think they have a key if they put a > "magical, all purpose" ID column on a table by using IDENTITY. This is > an exposed physical locator based on the internal state of the machine > and has nothing whatsoever to do with the data model. It is not an > atribute of any entity. It is an attempt to mimic a sequential file's > record numbering in an RDBMS. > > Also, why don't you learn the Standard syntax for UPDATEs so your code > will be predictable and portable? That silly FROM clause has all kinds > of problems. Start using the AS operator instead of = . > > Based on your recent postings, you have not bothered to read anything > on SQL or RDBMS. Color of What?? Tables?? Tables are things, not > attributes! You are copying needlessly proprietary syntax. > > UPDATE Ztemps > SET floob_color > = (SELECT floob_color > FROM Foobar AS Z > WHERE Z.table_name = Ztemps.table_name); > > A newsgroup is a bad place to learn the fundamentals, so I suggest that > you get a few books before you post anything again. > > >> I think that sometimes everybody has need help...<< And I tried to give it to you, but you don't want to hear the truth.You are posting thigns about foundations, not about SQL or programming. You have to get the foundations yourself. I teach SQL and RDBMS; I am not going to post an entire two-quarter lecture series in a newsgroup. You do not know the terminology and definitions yet. You don't know enough to ask a reasonable question. When you do not know what a key is, then you have not come to class with your homework. If you can find an old copy of the book, look up Fabian Pascal's "Relational Basics" which is a very good low-level intro to RDBMS. Celko,
Down boy, Down. =0} Send him a copy of your book before it goes out of print. Have a good 4th. Yoshi Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1120337800.474213.98970@g14g2000cwa.googlegroups.com... >>> I think that sometimes everybody has need help...<< > > And I tried to give it to you, but you don't want to hear the truth. > You are posting thigns about foundations, not about SQL or programming. > You have to get the foundations yourself. I teach SQL and RDBMS; I am > not going to post an entire two-quarter lecture series in a newsgroup. > You do not know the terminology and definitions yet. You don't know > enough to ask a reasonable question. When you do not know what a key > is, then you have not come to class with your homework. > > If you can find an old copy of the book, look up Fabian Pascal's > "Relational Basics" which is a very good low-level intro to RDBMS. > CELKO,
reading through this techNet discussion i read that lot's of question are about foundation as you write... I think, instead, there are lots of thing to know; foundation learnt year ago, worked in an another field, like Win server2000/2003 (----- MCSE ----) and ISA server and ExchServer 2000/2003 and Linux Red Hat, VB and VBA) and come back to SQL....may be sometimes somthing is forgot. I kow axactly about different between how you say things and attribute , I am not genie about SQL, but at the moment I wrote hundreds of code line and everything is working.. I believed to find people available to explain thing, instead I found someone who want to make teacher in place where teaching is not request. If I remeber right, the only code written here was only to understand what I asked .... without pay attention the sintyax (We are not in examination). I can naming cols as I want....then I 'll think naming everything (cols, PK and so on) at proper time I know exactly what is a PK and how to use I don' need your help thank...... When I asked an help I wrote only an example and how it sould be built.. I always helped people in other field in forum or discussion, never I have answered in this way as you did, (because I have respect about people) Learn ....to use respect for people... Show quote "Yosh" wrote: > Celko, > > Down boy, Down. =0} > > Send him a copy of your book before it goes out of print. Have a good 4th. > > Yoshi > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1120337800.474213.98970@g14g2000cwa.googlegroups.com... > >>> I think that sometimes everybody has need help...<< > > > > And I tried to give it to you, but you don't want to hear the truth. > > You are posting thigns about foundations, not about SQL or programming. > > You have to get the foundations yourself. I teach SQL and RDBMS; I am > > not going to post an entire two-quarter lecture series in a newsgroup. > > You do not know the terminology and definitions yet. You don't know > > enough to ask a reasonable question. When you do not know what a key > > is, then you have not come to class with your homework. > > > > If you can find an old copy of the book, look up Fabian Pascal's > > "Relational Basics" which is a very good low-level intro to RDBMS. > > > > > To quote Fabian Pascal, "Stay away from Celko!!!!"
http://www.dbdebunk.com/page/page/857309.htm You might also find this link informative: http://www.tdan.com/sms_issue33.htm Show quote "erncelen" <ernce***@discussions.microsoft.com> wrote in message news:095E000D-F7B9-424B-8500-29034E95B0D2@microsoft.com... > CELKO, > > reading through this techNet discussion i read that lot's of question are > about foundation as you write... > I think, instead, there are lots of thing to know; foundation learnt year > ago, worked in an another field, like Win server2000/2003 (----- MCSE ----) > and ISA server and ExchServer 2000/2003 and Linux Red Hat, VB and VBA) and > come back to SQL....may be sometimes somthing is forgot. > I kow axactly about different between how you say things and attribute , I > am not genie about SQL, but at the moment I wrote hundreds of code line and > everything is working.. > > I believed to find people available to explain thing, instead I found > someone who want to make teacher in place where teaching is not request. > > If I remeber right, the only code written here was only to understand what I > asked .... without pay attention the sintyax (We are not in examination). > > I can naming cols as I want....then I 'll think naming everything (cols, PK > and so on) at proper time > I know exactly what is a PK and how to use I don' need your help thank...... > > When I asked an help I wrote only an example and how it sould be built.. > > I always helped people in other field in forum or discussion, never I have > answered in this way as you did, (because I have respect about people) > > Learn ....to use respect for people... > > > > "Yosh" wrote: > > > Celko, > > > > Down boy, Down. =0} > > > > Send him a copy of your book before it goes out of print. Have a good 4th. > > > > Yoshi > > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > news:1120337800.474213.98970@g14g2000cwa.googlegroups.com... > > >>> I think that sometimes everybody has need help...<< > > > > > > And I tried to give it to you, but you don't want to hear the truth. > > > You are posting thigns about foundations, not about SQL or programming. > > > You have to get the foundations yourself. I teach SQL and RDBMS; I am > > > not going to post an entire two-quarter lecture series in a newsgroup. > > > You do not know the terminology and definitions yet. You don't know > > > enough to ask a reasonable question. When you do not know what a key > > > is, then you have not come to class with your homework. > > > > > > If you can find an old copy of the book, look up Fabian Pascal's > > > "Relational Basics" which is a very good low-level intro to RDBMS. > > > > > > > > > Fabian's second and third item in that same link are equally interesting,
given the copious amounts of discussion given to "Natural vs. Surrogate Keys" that takes place almost every day in this group: 2. Do not confuse logical and physical levels!!! Performance is determined exclusively at the physical level and has nothing to do with choice of keys, which is a purely logical issue. Now, if you get worse performance with a certain type of key rather than another, that is a product implementation issue and should not be confused with the key choice. 3. We do not recommend the use of surrogate keys as a rule, but only in some rare circumstances and for very specific reasons. Chapter 3 in my book gives ah explanation of the criteria for choosing keys, which also help determining when those special circumstances apply. Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:edZJNCFgFHA.2152@TK2MSFTNGP14.phx.gbl... > To quote Fabian Pascal, "Stay away from Celko!!!!" > http://www.dbdebunk.com/page/page/857309.htm > > You might also find this link informative: > http://www.tdan.com/sms_issue33.htm > > > "erncelen" <ernce***@discussions.microsoft.com> wrote in message > news:095E000D-F7B9-424B-8500-29034E95B0D2@microsoft.com... >> CELKO, >> >> reading through this techNet discussion i read that lot's of question are >> about foundation as you write... >> I think, instead, there are lots of thing to know; foundation learnt year >> ago, worked in an another field, like Win server2000/2003 (----- > MCSE ----) >> and ISA server and ExchServer 2000/2003 and Linux Red Hat, VB and VBA) >> and >> come back to SQL....may be sometimes somthing is forgot. >> I kow axactly about different between how you say things and attribute , >> I >> am not genie about SQL, but at the moment I wrote hundreds of code line > and >> everything is working.. >> >> I believed to find people available to explain thing, instead I found >> someone who want to make teacher in place where teaching is not request. >> >> If I remeber right, the only code written here was only to understand >> what > I >> asked .... without pay attention the sintyax (We are not in > examination). >> >> I can naming cols as I want....then I 'll think naming everything (cols, > PK >> and so on) at proper time >> I know exactly what is a PK and how to use I don' need your help > thank...... >> >> When I asked an help I wrote only an example and how it sould be built.. >> >> I always helped people in other field in forum or discussion, never I >> have >> answered in this way as you did, (because I have respect about people) >> >> Learn ....to use respect for people... >> >> >> >> "Yosh" wrote: >> >> > Celko, >> > >> > Down boy, Down. =0} >> > >> > Send him a copy of your book before it goes out of print. Have a good > 4th. >> > >> > Yoshi >> > >> > "--CELKO--" <jcelko***@earthlink.net> wrote in message >> > news:1120337800.474213.98970@g14g2000cwa.googlegroups.com... >> > >>> I think that sometimes everybody has need help...<< >> > > >> > > And I tried to give it to you, but you don't want to hear the truth. >> > > You are posting thigns about foundations, not about SQL or > programming. >> > > You have to get the foundations yourself. I teach SQL and RDBMS; I >> > > am >> > > not going to post an entire two-quarter lecture series in a >> > > newsgroup. >> > > You do not know the terminology and definitions yet. You don't know >> > > enough to ask a reasonable question. When you do not know what a key >> > > is, then you have not come to class with your homework. >> > > >> > > If you can find an old copy of the book, look up Fabian Pascal's >> > > "Relational Basics" which is a very good low-level intro to RDBMS. >> > > >> > >> > >> > > > >> To quote Fabian Pascal, "Stay away from Celko!!!!" << If you think I am abrasive, wait until you run into Fabian Pascal :)Ask anyone who has invited him as a speaker to a conference, google his replies and posting in newsgroups and forum, etc. . I love that quote. It has gotten me tens of thousands of dollars in consulting work over the years.  
Other interesting topics
|
|||||||||||||||||||||||
