|
How can I write a query which updates the top 200 rows of a table and then selects those rows? Here is what I have so far:
UPDATE TableX
SET Column2= 'XXXXX'
FROM
(
SELECT TOP 200 Column1, Column2, Column3, Column4
FROM TableX
WHERE Column2 IS NULL
)AS RetrievedRows
WHERE TableX.Column2= RetrievedRows.Column2
In the subquery I grab the top 200 rows, in the parent query I update those rows. But how can I select those rows that were just updated?
CodingYoshi
Visual Basic is for basic people, C# is for sharp people. Farid Tarin '07
|
|
|
|
|
You could consider adding a 'last updated' timestamp, if you don't mind the bit extra overhead. That would simplify selecting the last updated records a tad 
I are troll
|
|
|
|
|
hi
I have a general question
if I write
row[0][0] = 5;
does a boxing occurs ?
should I write (from perfomance look )
row[0][0] = (object)5;
|
|
|
|
|
What database are you using
what is boxing - not in any SQL I know of
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i'm using SQL 2005
but the boxing is not done by the SQL server
it's done by the CLR
|
|
|
|
|
Sorry, can't help you there I don't use the cls, I know of no one who does. Stupid idea really, another layer of abstraction so the poor dev does not need to learn TSQL.
However I use convert.to????? in C# rather than boxing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In C# there are two types of Convertion is used.
<b>Boxing :convert ValueTypes to Reference Types
UnBoxing :Reference Types to convert ValueTypes </b>
From Your code
row[0][0] = 5; ---> This is implicit Boxing
row[0][0] = (object)5; ---> This is Explicit Boxing
You can use any of these to the Conversion.
This is General DataBase MessageBoard. So don't ask C# Questions here use C# MessageBoard.
Bye,
Jai
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
hello
How can you get a complex multiline dynamic T-SQL to execute, I tried ";" don't work, SQL Anaylzer doesn't recognize line breaks.
SET @SQL =
'DECLARE @ThisParentId bigint;
DECLARE @ThisParentType varchar(255);
DECLARE @CountParent int;
DECLARE $CURSOR_NAME$ CURSOR FOR
SELECT ParentId,ParentType
FROM HIERARCHYMAP
WHERE
ChildId = $OBJECTID$
AND ChildType = ''$OBJECTCLASSIFIER$'';
OPEN $CURSOR_NAME$;
FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;
WHILE @@FETCH_STATUS = 0;
BEGIN
SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType;
IF (@CountParent>0)
BEGIN
EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$'';
END;
FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType;
END;
CLOSE $CURSOR_NAME$;
DEALLOCATE $CURSOR_NAME$;
'
SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)
SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )
SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )
SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )
PRINT @SQL
EXECUTE @SQL
Thanks
dev
|
|
|
|
|
<br />
<br />
CREATE PROCEDURE spRecursiveGetParents <br />
(<br />
@ObjectId bigint, <br />
@ObjectClassifier varchar(255),<br />
@TempId varchar(36)<br />
)<br />
AS<br />
BEGIN<br />
DECLARE @CursorName varchar(100)<br />
DECLARE @SQL varchar(4000)<br />
DECLARE @NSQL nvarchar(4000)<br />
<br />
SET @CursorName = 'spGetParents_cursor_' + CAST(@ObjectId as varchar(20)) + '_' + REPLACE(CAST(@ObjectClassifier AS varchar(70)), '.', '_')<br />
<br />
PRINT @CursorName<br />
<br />
INSERT INTO [dbo].[TMP_HierarchyMap] <br />
(<br />
ParentId,<br />
ChildId,<br />
ParentType,<br />
ChildType,<br />
ParentDAOType,<br />
ChildDAOType,<br />
[Description],<br />
CreateDate,<br />
CreatedBy,<br />
TmpUID,<br />
ReferencedId<br />
)<br />
SELECT <br />
ParentId,<br />
ChildId,<br />
ParentType,<br />
ChildType,<br />
ParentDAOType,<br />
ChildDAOType,<br />
[Description],<br />
CreateDate,<br />
CreatedBy,<br />
@TempId,<br />
Id<br />
FROM HIERARCHYMAP M1<br />
WHERE<br />
ChildId = @ObjectId<br />
AND<br />
ChildType = @ObjectClassifier<br />
AND<br />
NOT EXISTS(SELECT 1 FROM TMP_HIERARCHYMAP M2 WHERE M1.ParentId=M2.ParentId AND M1.ParentType=M2.ParentType AND ChildId=@ObjectId AND ChildType=@ObjectClassifier AND TmpUID=@TempId)<br />
<br />
SET @SQL = <br />
'DECLARE @ThisParentId bigint<br />
DECLARE @ThisParentType varchar(255)<br />
DECLARE @CountParent int<br />
<br />
DECLARE $CURSOR_NAME$ CURSOR FOR <br />
SELECT ParentId,ParentType <br />
FROM HIERARCHYMAP <br />
WHERE <br />
ChildId = $OBJECTID$<br />
AND ChildType = ''$OBJECTCLASSIFIER$''<br />
<br />
OPEN $CURSOR_NAME$<br />
FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
<br />
SELECT @CountParent = count(1) FROM HIERARCHYMAP WHERE ChildId = @ThisParentId AND ChildType = @ThisParentType<br />
IF (@CountParent>0)<br />
BEGIN<br />
EXEC spRecursiveGetParents @ThisParentId, @ThisParentType, ''$TEMPID$''<br />
END<br />
FETCH $CURSOR_NAME$ INTO @ThisParentId, @ThisParentType<br />
END<br />
<br />
CLOSE $CURSOR_NAME$<br />
DEALLOCATE $CURSOR_NAME$<br />
'<br />
SET @SQL = REPLACE(@SQL, '$CURSOR_NAME$', @CursorName)<br />
SET @SQL = REPLACE(@SQL, '$OBJECTID$', CAST(@ObjectId as varchar(20)) )<br />
SET @SQL = REPLACE(@SQL, '$OBJECTCLASSIFIER$', CAST(@ObjectClassifier as varchar(70)) )<br />
SET @SQL = REPLACE(@SQL, '$TEMPID$', CAST(@TempId as varchar(36)) )<br />
<br />
SET @NSQL = CAST(@SQL AS nvarchar(4000))<br />
<br />
PRINT @NSQL<br />
<br />
<br />
EXEC sp_executesql @NSQL<br />
END<br />
dev
|
|
|
|
|
I've noticed that some of the aspnet_ tables for security (e.g. aspnet_Roles) uses a primary key of a uniqueidentifer data type which is a very long number.
When should someone use a primary key, where the datatype is a uniqueidentifier as opposed to int and set IsIdentity=Yes?
Thanks
|
|
|
|
|
The main reason is because a unique identifier is unique for any database.
One advantage is that if you have to merge databases then you won't class with ID values. With an int then the possibility is quite high unless you carry out a lot of work to prevent it.
Another is security. Because unique identifiers are randomly generated that means a person who has one identifer cannot easily guess the next on in the sequence or find adjacent rows in the database. They could randomly guess, but with the speed of current technology they'll be there a while.
|
|
|
|
|
I am working on sql server 2008 I have a table with a varbinary(max) filed
I want to store a row of this table in ather table in xml filde so when I write a query and set in the end of query (for xml auto) an error was occered what can i do
|
|
|
|
|
What did the error message say?
I are troll
|
|
|
|
|
FOR XML AUTO could not find the table owning the following column 'Logo' to create a URL address for it. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.
|
|
|
|
|
According to the syntax mentioned in this[^] blogpost, I'd assume that your statement would work if you add the BINARY BASE64 option like this;
FOR XML AUTO, BINARY BASE64
I are troll
|
|
|
|
|
|
I want , the data are maintain two database for example database1 and
database2. Now must maintain same data in both database. If any changes in
database1 through another application , i want change database2 (or Notify, If
have any special query or any thing else please share with me ). I am using
Sql Server 2005 database , How can handle this
Please help me....
|
|
|
|
|
Google for 'sql server replication'
|
|
|
|
|
--> how to determine that a data (records of two columns) exist in a table?
for example:
CustomerID Customer Name City
1 John New York
2 Adam Detroit
3 Brayan Washington
--> i want to know if ("Adam" and "Detroit") exist in the same row or not....
J A Nasir K
modified on Sunday, April 12, 2009 11:45 AM
|
|
|
|
|
This is such a basic operation, that I can only suggest that you do some research on T-SQL. Particularly Select and Where. Try a few things out and come back if what you try fails.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
use the exists statement.
|
|
|
|
|
Hi
I am using SQL Server 2008.I want to write a stored proc to get values from three tables and each of these will have equal no of rows always.
so i want to merge the row at each level into a single row and insert into a new table (datatypes of source and destination columns are same)
For e.g.
Row1 from table1 and row1 from table2 and row1 from table3 into a single row1 in resulttable
Help would be appreciated
Thanks and regards
Abhi
|
|
|
|
|
You could paste the individual select-statements into a new table like this;
SELECT INTO resulttable (
SELECT TOP 1 [Field1] FROM [table1]
UNION
SELECT TOP 1 [Field1] FROM [table2]
UNION
SELECT TOP 1 [Field1] FROM [table3]
)Note that this will only work if the datatypes of source and destination columns are the same
--that forms a single table using the first row from each table, not a single row--
My bad for not reading the question properly. You'd want a result that's something like this;
SELECT [Table1Id], [Table2Id]
FROM [Table1],
[Table2]Linking the first row of table1 to the first row of table2, and so forth? Are the rows numbered in the database?
I are troll
modified on Sunday, April 12, 2009 6:31 AM
|
|
|
|
|
Hi,
I found a solution but i dont know if its the best performance wise.
Note :dbo.COM_FN_SplitString : it splits a string based on the delimiter passed
/*****Code*****/
Declare @table1 as table(rowID int IDENTITY(1,1),GroupId int)
Declare @table2 as table(rowID int IDENTITY(1,1),Cost Numeric(10,2))
Declare @table3 as table(rowID int IDENTITY(1,1),Currency Varchar(max))
Declare @resultTable as table(GroupId int,Cost Numeric(10,2),Currency Varchar(max))
Insert into @table1(GroupId) SELECT Convert(int,s) as GroupId From dbo.COM_FN_SplitString('1,2,3',',')
Insert into @table2(Cost) SELECT Convert(Numeric(10,2),s) From dbo.COM_FN_SplitString('30.0,35.5,40.0',',')
Insert into @table3(Currency) SELECT s From dbo.COM_FN_SplitString('Dollar,Pound,Dollar',',')
Insert into @resultTable(GroupId,Cost,Currency)
select GroupId,Cost,Currency From
@table1 as t1
inner join
@table2 as t2
on t1.rowID = t2.rowID
inner join
@table3 as t3
on t3.rowID = t1.rowID
select * From @resultTable
/*****Code*****/
|
|
|
|
|
Looks good - I think that it will perform quite nicely for four rows
I are troll
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
