|
A table containing three columns (to which you must add a primary key - unless size and colour are the primary key)
Size Colour Quantity
S 1 10
S 2 4
S 3 1
M 1 20
M 2 5
M 3 0
....
Your matrix is a pivoted varient of what I have shown above.
Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hai,
The following is my queries.
1: Is it possible to have check constraint with a local variable?
2: Given the following scenario:
I have three columns in a table ,say ID int,name varchar, address varchar, and I added 10 records into that table keeping ID colums NULL.Later I want to add values to ID columns 1 thru 10 but with a single update query.(It doesn't means you are bound to use only 1 satement in the batch, no loop permitted )
Please show me a right way.Thanks very much.
Thank You,
Rahul.P.Menon.
|
|
|
|
|
Hi,
I'm creating a Win Form that uses a MS Access Database.
I'm new to database programming and wanted to know what advantages, besides referential integrity, is there to creating a Foreign Key? I'm wondering if creating a Foreign Key helps me with SQL statements in my code? Or would creating Foreign Keys not change SQL statements like this:
AND Products.CustomerID=Customers.CustomerID
thanks,
Ron
|
|
|
|
|
I can't think of another reason besides referential integrity. Foreign keys are constraints and do not change SQL statements. They just guarantee that Products.CustomerID will match the CustomerID in the Customers table. In other words, you shouldn't be able to delete Customer 1 from the Customers table if Customer 1 exists in the Products table.
|
|
|
|
|
Thanks Gerald,
I thought that, but wanted to ask anyway.
Ron
|
|
|
|
|
I'm trying to optimize some sql code, and I figure there is an easier way to do this, but since I'm still relatively new to SQL, I'd like some help if possible.
I have a table that has 4 relevant values, the table's id key (TKey, int), a parent key (PKey, int), a Type (Type, int), and a Locked value(Locked, bool).
I'm trying to return the locked value of the maximum local key of a certain set of types.
What I've come up with so far:
SELECT
Locked
TKey
FROM
Table
WHERE
TKey = (Select Max(TKey) from Table) where (Type = 0 or Type = 1) AND PKey = @Pkey)
Is there a better way to do this, or am I doing it correctly? It just seems that I shouldn't be querying a table within a query to that table. Maybe something with 'Top 1' or 'Group By'?
|
|
|
|
|
Drew McGhie wrote: Is there a better way to do this, or am I doing it correctly?
Other than the extra closing bracket it looks okay to me. If you need to optimising it then look at the query plan in Query Analyzer because it never ceases to amaze me what SQL Server thinks is optimal in a given situation - I've also had one change somewhere in the database cause an existing query to start running a lot slower than before. I feel that database optimisation is an on-going process rather than something you do once when you are doing your initial development.
Anyway, an alternative to your code:
SELECT TOP 1 Locked, TKey
FROM Table
WHERE Type IN (0,1)
AND PKey = @Pkey
ORDER BY TKey DESC
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Does anybody know of a good way to implement a trigger for selects on a sql server 2000 table? I know it's not natively supported and putting it in an SP won't work for this situation. I'm curious if anybody has come up with any good workarounds.
|
|
|
|
|
Stored proc is likely the only way to force selects to trigger some action. You probably also have to remove the select privilege from the schema owner.
What's the trigger supposed to do?
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
NOTED: The government now loses money on each penny it produces thanks to the soaring price of zinc -- the main component of the copper-coated coins. The cost of the metals in a penny rose to 0.8 cents last week, and the government spends at least another 0.6 cents to mint each one-cent coin. [The New York Times]
|
|
|
|
|
Chris Meech wrote: Stored proc is likely the only way to force selects to trigger some action. You probably also have to remove the select privilege from the schema owner.
I was thinking the same thing, but was curious if anybody had any workarounds.
Chris Meech wrote: What's the trigger supposed to do?
Basically access auditing. A data owner vendor of our decided out of nowhere that they want auditing of data access. For whatever reason I was told that security restrictions are not adequate in determining who has data access (go figure...  ).
|
|
|
|
|
This is my code for INSERT data to my database:
////////////////////////////////////////////////////
Dim strconn As String = "server=fry;uid=user;password=sql;database=db"
Dim dbconn As SqlConnection = New SqlConnection(strconn)
Try
Dim dbinsert As SqlCommand = New SqlCommand("INSERT INTO Kunder(Namn,Ort) VALUES('" & txtFname.Text & "','" & txtPlace.Text & "')", dbconn)
dbconn.Open()
dbinsert.ExecuteNonQuery()
dbconn.Close()
Catch ex As Exception
Label1.Text = ex.ToString()
End Try
Refresh()
////////////////////////////////////////////////////
But how do I UPDATE a post??
-- modified at 9:18 Wednesday 3rd May, 2006
|
|
|
|
|
You need to use an UPDATE statement.
"UPDATE Kunder SET Namn='" & txtFname.Text & "',Ort='" & txtPlace.Text & "' WHERE Namn='" & cOldNamnValue & "'"
This assumes that Namn is unique in the table (otherwise it will update all records where Namn matches cOldNamnValue). If the Namn field is not unique, then you need to have some kind of identity field, and use that in the WHERE clause of your UPDATE statement.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
This code is susceptable to a SQL Injection Attack - At the very least the injected values should be replaced with parameters. See SQL Injection Attacks and Tips on How To Prevent Them[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Colin Angus Mackay wrote: This code is susceptable to a SQL Injection Attack
True
I keep forgetting to remind people of that. Thanks.
BTW: Good article.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi Everyone,
Can one tell tell what is the most effective way of inserting multiple data from dataset without using loop in .NET
leo
|
|
|
|
|
Hi database people.
So how can I remove duplicate rows from a table? So far I have been using:
select distinct * into NewTable from OldTable
and then removing the original table and renaming the new table to the original name.
Anyone know a better way? Thanks..
Regards,
Rob Philpott.
|
|
|
|
|
check this
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|
|
|
|
|
Nice one. Thanks!
Regards,
Rob Philpott.
|
|
|
|
|
|
Search comments[^]
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Good point. I must learn how to do this...
Regards,
Rob Philpott.
|
|
|
|
|
Hi EveryOne,
I have created 1 table in my DB named F-orgs containg only 1 field F_orgs. And it’s the primary key. Its Data Values are
F_orgs
F01
F02
F03
F04
and other table Item_Details having F_Orgs as the foreign key.
and One Item can be used by multiple F_Orgs.
Now the prob is that:
My table Item Details is not accepting multiple values under F__orgs field. It is only taking 1 value like
Item_No F_orgs
1414-3635 F01
1414-3636 F02
I want this
Item_No F_orgs
1414-3637 F01, F02
1414-3638 F02, F03
And it is but obvious while inserting it is checkn values with F_Orgs Table (Relationship Bw F_Orgs and Item_Details Table)
Rite. So is my logic going wrong somewhere.
Can anyone have any idea, where i m going wrong.
Plz tell.
Regards
|
|
|
|
|
I think your insert should be trying to get records like this:
1414-3637 F01
1414-3637 F02
1414-3638 F02
1414-3638 F03
When you put comma separated values into a field, the DB checks that the string as a whole matches a single entry in the F_Orgs table.
|
|
|
|
|
Hi there,
2 of my table fields is an int (winery ID), and the other is ntext (winery description). The size of the int is 4, and that of ntext is 16.
When passing the parameters in a stored procedure, I thought that using a size 4 or 16 would pass the whole number, or text, but surely this is not the case. When specifying a size of 16, it cuts off on the 16th character. So what I am going to do is use objWinery.Description.Length. But how do I then pass the size/length of the int?
I have a function that createds my parameter like:
AddParameterToSQLCmd(objCmd, "@Description", SqlDbType.NText, 16, ParameterDirection.Input, objWinery.Description);
I hope this makes sense.
Regards,
ma se
|
|
|
|
|
The length of the int will always be 4 (byte size in the DB).
The ntext data is stored separately from the rest of the table data, and does not take a length parameter. The length of 16 you see in the DB refers to the byte size of the reference the db saves to the external data.
Hope that made sense...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
