|
Hey Guys,
Lets say we have a table T1 that have a numeric field F:
Table T1 (int ID, int F)
I need to update this table and set F to the MAX +1 to insure uniqueness:
UPDATE T1
SET F = (SELECT ISNULL(MAX(F),0) + 1)
WHERE ID IN(1,2)
The thing is I end up with both IDs 1 and 2 having the same value for F. I remember doing but what do I have to change so that Max is calucated for row in T1 so that if my initial table is like:
ID F
1 1
2 2
Running the update query will get the following result
ID F
1 3
2 4
Thanks.
|
|
|
|
|
UPDATE T1<br />
SET F = (SELECT top 1 ISNULL(MAX(t2.F),0) + 2 from T1 as t2 where t2.id=T1.ID)<br />
WHERE ID IN(1,2)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Any easy way to do that is:
DECLARE @I INT
SELECT @I = MAX(F) FROM T1
UPDATE T1
SET @I = F = @I + 1
Change T1 and F for your actual names.
|
|
|
|
|
I have a table similar to the following :-
Id, BigInt, IsIdentity
Name, VarChar(50)
ParentId, BigInt, Allows Nulls
Therefore I could have data as follows
Id Name ParentId
-- ---- --------
1 Fred Null
2 Fred 1
3 Fred 1
4 Fred 2
5 Fred 4
Therefore data is structured as follows :-
1, Fred
..2, Fred
....4, Fred
......5, Fred
..3, Fred
What I need to do, (and this is where my weak T-SQL lets me down) is when the Name is change for row 1, an update cascades through the table and updates and related row i.e. where ParentId = Id of row updated.
My thoughts are to use an trigger on Update, but I need some help with the code.
Also, if I understand the update tigger correctly, and the option is enabled at Database level, the rows being updated by the trigger will cause the trigger to fire again, thus the update with cascade down the tree.
Any assistance is much appreicated.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Hi, try something like this:
CREATE TRIGGER [Table1_Update] ON [dbo].[Table1]
FOR UPDATE
AS
IF EXISTS(SELECT * FROM inserted)
UPDATE Table1 SET [Name] = I.[Name] FROM Table1 T INNER JOIN inserted I ON T.ParentID = I.ID
You're right in that the trigger recurse option needs to be enabled (disabled by default), but be aware that you're limited to 32 levels in the recursion stack (sql server 2000 - don't know limit in SQL2005), so you're tree must be limited to max 32 levels
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for the example code and the additional info.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Steve
I have a nasty feeling you are setting yourself up for a disaster. I don't understand your requirement but some of your statements are suspect.
the field name should never be used in a relationship. It should be stored once and the ID used to define your heirachy. You need 2 tables for this operation.
Name Table
ID int, Name varchar(?)
Link Table
ID int, ParentID int
Id ParentId
-- --------
1 Null
2 1
3 1
4 2
5 4
and join out to the name table (twice for ID and ParentID).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understand your concerns and thanks for raising them. I did not go too deeply into what I need to achieve because I did not think it relevent to the problem (i.e. my lack of knowledge) at the time.
Just to make things clear, the problem I have is will a database created and maintained by a third-party product, namely Sage SalesLogix CRM package. I have a requirement where the principle contact at a parent account is also the principle contact at each associated child account, but SalesLogix does not cater for this requirement as standard. Therefore some coding has been done on SalexLogix that when a child account is associated with the parent account, then principle contact of the parent is copied to child. So far so good.
Now the problem is, that when the details of the contact is changed, it is only for the that one record. This is where the trigger comes into play. What I want to do is when the details of a contact is changed, all duplicate(ish) records are updated to reflect the change, hence my example showing a column called 'Name' with the same value.
Thanks again for you concern.
Best regards
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Steven, this should be held us as a classic DB design error 101 - typical of Sage.
Are you attempting this in SageLogix (not), if so then my sympathy.
I would naturally [avoid] triggers because I hate them  . If you can inject an Exec into the save process I would use a stored proc to synch the data based on the ParentID and new contact name. I presume the user can update the contact from either the child or the parent.
Good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using following query ..
select siid as Sid,biid as Bid,(select iname from Ol_items where iid=siid) as SIName,(select iname from Ol_items where iid=biid) as BIName from Ol_Wishlist
ouput is
Sid Bid SIName BIName
10 27 NULL Spice
here SINAME = null value
but I want if not null display the data ..if null,Instead of NULL Some text like NO and
Sid Bid SIName BIName
10 27 NO Spice
anybody know plz reply me..
thanks
Rajendran.AL
|
|
|
|
|
This should do it (around each of the subqueries):
COALESCE((select iname ...), 'NO') as ...
Look up the COALESCE function in the documentation for details.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Its working fine.....Thanks alot...
Rajendran.AL
|
|
|
|
|
Personally I would use a CASE for that, although as the other poster suggested a COALESCE would work.
|
|
|
|
|
|
Giorgi Dalakishvili wrote: You can use ISNULL
yes, but coalesce is standard, so I prefer it to isnull which is not.
|
|
|
|
|
You can use coalesce function:
select siid as Sid,biid as Bid,coalesce ((select iname from Ol_items where iid=siid),'NO') as SIName,(select iname from Ol_items where iid=biid) as BIName from Ol_Wishlist
|
|
|
|
|
Hey guys
ive got a table in my DB, employee... this table has a column "EndOfEmployment" which is DATETIME and obviously nullable
yesterday while trying to write a query to count the number of "Active" employees i came across a strange issue... the query is as follows
SELECT count(Emp_EmployeeNumber)
FROM Employee
WHERE Emp_EmploymentStatus = 'A' OR (Emp_EmploymentStatus = 'T' AND (Emp_EndOfEmployment = NULL OR Emp_EndOfEmployment < @todaysDate))
i noticed that when the EndOfEmployment field does not get data in the insert statement, it DOES NOT get a value of NULL... so i tried giving it a value of NULL in the insert statement and that still didnt work.
then i tried Emp_EndOfEmployment = '' instead of Emp_EndOfEmployment = NULL in the WHERE clause but still no luck
Does anyone have any ideas?
Thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
You can't normally compare anything to NULL with the = operator (the result isn't true or false, but NULL - anything you do on a NULL is NULL). You need to use IS NULL / IS NOT NULL or COALESCE.
If you KNOW that you're looking for NULL, do this:
... WHERE (thefield IS NULL) OR (thefield < @limit)
or:
... WHERE COALESCE(thefield, '1900-01-01') < @limit
That's assuming you have a suitable "NULL equivalent" value to use (as in 1900-01-01). Note that the YYYY-MM-DD layout is unambiguous and always converts automatically to datetime in SQL, regardless of regional settings. Any other format needs an explicit CONVERT with a style parameter to work internationally. Also - the datetime/smalldatetime are the only times where picking a good minimum value is problematic, because:
datetime: 1753-01-01 is the first valid date.
smalldatetime: 1900-01-01 is the first valid date.
Numeric and string columns are easier.
BONUS:
Sometimes you don't even know what you're looking for (such as in a parameterized query). This is a good one (assuming GroupId can be null but never 0 - as in a foreign key relation with an identity field):
... WHERE COALESCE(GroupId, 0) = COALESCE(@param1, 0)...
Figuring that out a) saved me a lot of IFs and CASEs, and b) made me feel pretty stupid about not having done it sooner...
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
How do I obtain the size of a particular database schema, not just a table, programatically using C# or VB.NET? Thanks.
|
|
|
|
|
I'm assuming that you use MS SQL Server,
Execute the sp_helpdb 'DBNAME' stored procedure. It will return 2 result sets, the first contains the db_size field which is the total size (data and log) of the database.
|
|
|
|
|
Hi all.
I have written a simple application in vb.net (2.0) that reads data from an excel sheet (previously stored in focus databases) and stores it into a sql server database. I am using typed datasets and table adapters to store the data. I have an "Import" button whose event handler does the following in turn.
1. connect to the excel sheet through an ole db conn
2. read the contents in a while loop using the executeReader() method
3. store the values in variables where necessary conversions and validations are performed.
4. call an insert method in the associated dataset tableAdapter that stores these processed values in the sql db.
This is all working as it should. Now I need to check for existing records before I import the values - Ignore completely if the value is found.
According to msdn, I should be able to use my tableAdapters update method by passing a dataSet/Table/Row(s). So this is how I tried to tackle it.
Legend:
lbf = my DataSet
lbf_COKEnCOLE = the Database table associated with lbf DataSet
GetRecordByIdDateLab = runs the following SQL Query on the data
SELECT *
FROM lbf_COKEnCOLE
WHERE (ORIGINATOR_ID = @ORIGINATOR_ID) AND (SMPL_DTE = @SMPL_DTE) AND (LAB_NUM = @LAB_NUM)
(The actual sql refers to all the columns by names instead of using *)
Dim tableadapter As New lbfTableAdapters.lbf_COKEnCOLETableAdapter
Dim dataTable As lbf.lbf_COKEnCOLEDataTable = Nothing
'check for existing record by using GetRecordByIdDateLab method
dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)
If Not dataTable Is Nothing Then
If dataTable.Rows.Count > 0 Then
If Not dataTable(0).ORIGINATOR_ID = Nothing Then
'Row = dataTable(0)
dataTable(0).TURN = TURN
dataTable(0).SMPL_ANALYS_HOUR = SMPL_ANALYS_HOUR
dataTable(0).SMPL_ANALYS_MIN = SMPL_ANALYS_MIN
dataTable(0).MOISTURE = MOISTURE
dataTable(0).S = S
dataTable(0).VM = VM
dataTable(0).ASH = ASH
dataTable(0).HARDNESS = HARDNESS
dataTable(0).STABILITY = STABILITY
dataTable(0).QRT_TUMBLE = TUMBLE_30M
dataTable(0).APTSPC_GRAV = APTSPC_GRAV
dataTable(0).FREE_SWL_I = FREE_SWL_I
dataTable(0).PULV8TH = PULV8TH
dataTable(0).QRT_PULV = QRT_PULV
dataTable(0).SCR_4 = SCR_4
dataTable(0).SCR_3 = SCR_3
dataTable(0).SCR_2 = SCR_2
dataTable(0).SCR_1NHALF = SCR_1NHALF
dataTable(0).SCR_1 = SCR_1
dataTable(0).SCR_3QRT = SCR_3QRT
dataTable(0).SCR_HALF = SCR_HALF
dataTable(0).SCR_38THS = SCR_38THS
dataTable(0).SCR_QRT = SCR_QRT
dataTable(0).QRT_PULV = QRT_PULV
dataTable(0).SCR_8TH = SCR_8TH
dataTable(0).SCR_20M = SCR_20M
dataTable(0).SCR_30M = SCR_30M
dataTable(0).SCR_50M = SCR_50M
dataTable(0).SCR_100M = SCR_100M
dataTable(0).SCR_PAN = SCR_PAN
dataTable(0).SCR_QRT_PLUS = SCR_QRT_PLUS
dataTable(0).SAMPL_LOC = SAMPL_LOC
tableadapter.Update(dataTable) '<<THIS IS WHERE IT CRASHES WITH "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll
End If
'The following tableAdapter works perfectly whenever the condition is true (for new entries that is)
ElseIf dataTable.Rows.Count = 0 Then
tableadapter.InsertQuery(counter, ORIGINATOR_ID, SMPL_DTE, LAB_NUM, TURN, SMPL_ANALYS_HOUR, SMPL_ANALYS_MIN, _
MOIS_GRAV, MOISTURE, S, VM, ASH, HARDNESS, STABILITY, QRT_TUMBLE, TUMBLE_30M, APTSPC_GRAV, _
FREE_SWL_I, PULV8TH, QRT_PULV, SCR_4, SCR_3, SCR_2, SCR_1NHALF, SCR_1, SCR_3QRT, SCR_HALF, _
SCR_38THS, SCR_QRT, SCR_8TH, SCR_20M, SCR_30M, SCR_50M, SCR_100M, SCR_PAN, SCR_QRT_PLUS, SAMPL_LOC)
End If
End If
****************************************
More details:
I have DataTable under "Watch" and the value for it shows up as "dataTable has not been declared". This ofcourse is misleading because it is, plus intellisense picks it up where I'm updating the column values by referencing them with dataTable(0).columnName
I have been wondering if
dataTable = tableadapter.GetRecordByIdDateLab(ORIGINATOR_ID, SMPL_DTE, LAB_NUM)
does what its supposed to do, ie. return a dataTable with the matching record. I tried to recieve the data in a row but it only allowed it to be saved in a datatable instantiated in this manner. Furthermore, when I read the values read into the datatable, each column value shows up only as lbf_COKEnCOLE.column (something to this effect) in curly braces. Almost everything else thats returned, like index values and what not that I suppose is used to keep the schema of the table has errors in it.
Any suggestions and help would be greatly appreciated!! I am trying to avoid suddenly using command objects when I'm doing everything else through a data Access layer. lastly, I have read and reread this msdn page
http://msdn.microsoft.com/en-us/library/ms233819(VS.80).aspx
and am trying to do everything as shown here, obviously to no avail 
All .net people out there, if you would help this awkward programmer, it would be greatly appreciated! Thanks
~Ghazanfar
|
|
|
|
|
Hi,
We have problem with our replication process which replicates notes data to SQL.
NoteSQL is used to create the ODBC connection between notes and SQL.
Initially the replication process went well and data is replicated in SQL.
COM object is created to do the replication process instead of Agents.
Problem is we are struck with this and we don't know where to look for the solution as the person created this is not available.
we are using Lotus Notes 6.5 and SQL server 2003,all I can see is an exe running in the processes and as long as that exe is running tables should be replciated in SQL(which it did initially),but tables are not replicated in SQL..
Thanks for any sort of help with this problem.
|
|
|
|
|
Dear All,
I need to set the value of a variable with the result of a stored procedure. I need something on these lines:
set @productCount = exec procGetCount
I did not manage to get this to work. I am using Microsoft Sql Server 7 and 2000.
Thanks a lot,
Chris
|
|
|
|
|
You can't (in those versions at least) that I know of.
Either you have to make it into a function or use output parameters.
|
|
|
|
|
in sql 2000 sp4(mabye in other versions of 2000 but i dont have any to test on)
EXEC @var = procGetCount
but you have to RETURN the value out of the stored procedure for it to work and sql 2000 can only return int values.
example procedure code
DECLARE @var INTEGER
SET @var = 3
RETURN @var
you can also put SELECT @var before the return if you need to as well.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
