Simple SQL query not working as expected.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple SQL query not working as expected.

Author

Topic

dilip100hrs
Starting Member

1 Post

Posted - 2014-06-30 : 01:43:14

Hi Gurus,

I am new to SQL programming and I have written one simple code but it is not working as expected.
I have written one query to select TOP 500 records from DB and then I have used update to set the flag X form null for top 500.
But if there are 8000 unique records in DB then only 2700 records are coming in the target DB from source DB.What I doubt is that update is not happening in the same sequence as the select is happening resulting in updates of the records which never got selected and select of reocrds which never got updated .but since target DB doesnt allow dupliacte records the total records are less.Am I correct in assuming this? what could be the solution? thanks in advance.
my query:

select TOP 100 F6Accounts.dbo.TOLRecord.EmpCode,F6Accounts.dbo.TOLRecord.ConcID, F6Accounts.dbo.TOLRecord.eDate, F6Accounts.dbo.TOLRecord.eTime, F6Accounts.dbo.TOLRecord.Extra6,F6Accounts.dbo.TOLRecord.Status from F6Accounts.dbo.TOLRecord WHERE F6Accounts.dbo.TOLRecord.Extra6 is NULL

update TOP (100) F6Accounts.dbo.TOLRecord set F6Accounts.dbo.TOLRecord.Extra6 = 'X' WHERE F6Accounts.dbo.TOLRecord.Extra6 is NULL

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2014-06-30 : 04:35:54

In case of empcode column has primary key
Insert selected records in temp table and update the base table based on empcode from temp table and IS NULL crieteria on base table.

M.MURALI kRISHNA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-30 : 05:18:10

Without an ORDER BY you cannot tell which 100 rows are returned.
You can also use composable DML to update and output the result or use the OUTPUT alone.

WITH cteWork
AS (
	SELECT	EmpCode,
		ConcID,
		eDate,
		eTime,
		Extra6,
		[Status]
	FROM	dbo.TOLRecord
	WHERE	Extra6 IS NULL
)
UPDATE	cteWork
SET	Extra6 = 'X'
OUTPUT	inserted.EmpCode,
	inserted.ConcID,
	inserted.eDate,
	inserted.eTime,
	inserted.Extra6,
	inserted.[Status];

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources