Prev: Automation Script
Next: ANN: DBTyP.NET 2008 - Database Comparison Tool - New Release
From: Ian Boyd on 3 Jul 2008 17:14
i want to flag 500 rows in a staging table as the rows i'm going to process
right now. Once they've been processed they will be deleted, and another 500
will get marked. i don't really care which 500 rows, just 500 of them. If
the rows has a unique key, i would do an
UPDATE ImportStaging SET ImportFlag = 1
WHERE UniqueKeyColumnName IN (
SELECT TOP 500 UniqueKeyColumnName
FROM ImoprtStaging)

But since there's no unique key column, i cannot do that. Also, i don't
want to use the depricated
SET ROWCOUNT = 500

(unless you want to put $100,000 into escrow to cover the costs of updating
the import process to not use ROWCOUNT when it finally goes away)



The staging table DDL is as follows. Note the last column "ImportFlag" which
i want to set to "1" for 500 arbitrary rows:

CREATE TABLE ImportStaging (
[a] [uniqueidentifier] NOT NULL ,
[b] [uniqueidentifier] NULL ,
[c] [uniqueidentifier] NULL ,
[d [tinyint] NOT NULL CONSTRAINT [DF_ImportStaging_d] DEFAULT (0),
[e] [int] NULL ,
[f] [varchar] (50) NULL ,
[g] [varchar] (100) NOT NULL ,
[h] [varchar] (100) NOT NULL ,
[i] [varchar] (250) NULL ,
[j] [varchar] (100) NULL ,
[k] [varchar] (50) NULL ,
[l] [varchar] (50) NULL ,
[m] [varchar] (100) NULL ,
[n] [varchar] (50) NULL ,
[o] [datetime] NULL ,
[p] [varchar] (200) NULL ,
[q] [int] NULL ,
[r] [int] NULL ,
[s] [varchar] (200) NULL ,
[ImportFlag] [tinyint] NULL CONSTRAINT [DF_ImportStaging_ImportFlag]
DEFAULT (0)
)

CREATE INDEX [IX_ImportStaging_e] ON [dbo].[ImportStaging]([e], [f])


i can also say that the column tuple (e,f) _should_ be unique. My first
thought was:

UPDATE ImportStaging SET ImportFlag = 1
WHERE EXISTS (
SELECT TOP 500 *
FROM ImportStaging i2
WHERE i2.e = ImportStaging.e
AND i2.f = ImportStaging.f)

....but seems like it won't work - i have a feeling that the exists is
satisfied when i don't want it to be (i.e. always)

So i'm fiddling with something like:

UPDATE ImportStaging SET ImportFlag = 1
FROM ImportStaging
INNER JOIN (
SELECT TOP 500 e,f
FROM ImportStaging) dervivedTable
ON ImportStaging.e = derivedTable.e
AND ImportStaging.f = derivedTable.f

Does that seem reasonable? (The update statement...not the scenario i've set
up).

Or perhaps someone can think of an IN or EXISTS version, rather than a join.


From: Adi on 3 Jul 2008 17:43
On Jul 4, 12:14 am, "Ian Boyd" <ian.msnews...(a)avatopia.com> wrote:
> i want to flag 500 rows in a staging table as the rows i'm going to process
> right now. Once they've been processed they will be deleted, and another 500
> will get marked. i don't really care which 500 rows, just 500 of them. If
> the rows has a unique key, i would do an
> UPDATE ImportStaging SET ImportFlag = 1
> WHERE UniqueKeyColumnName IN (
> SELECT TOP 500 UniqueKeyColumnName
> FROM ImoprtStaging)
>
> But since there's no unique key column, i cannot do that. Also, i don't
> want to use the depricated
> SET ROWCOUNT = 500
>
> (unless you want to put $100,000 into escrow to cover the costs of updating
> the import process to not use ROWCOUNT when it finally goes away)
>
> The staging table DDL is as follows. Note the last column "ImportFlag" which
> i want to set to "1" for 500 arbitrary rows:
>
> CREATE TABLE ImportStaging (
> [a] [uniqueidentifier] NOT NULL ,
> [b] [uniqueidentifier] NULL ,
> [c] [uniqueidentifier] NULL ,
> [d [tinyint] NOT NULL CONSTRAINT [DF_ImportStaging_d] DEFAULT (0),
> [e] [int] NULL ,
> [f] [varchar] (50) NULL ,
> [g] [varchar] (100) NOT NULL ,
> [h] [varchar] (100) NOT NULL ,
> [i] [varchar] (250) NULL ,
> [j] [varchar] (100) NULL ,
> [k] [varchar] (50) NULL ,
> [l] [varchar] (50) NULL ,
> [m] [varchar] (100) NULL ,
> [n] [varchar] (50) NULL ,
> [o] [datetime] NULL ,
> [p] [varchar] (200) NULL ,
> [q] [int] NULL ,
> [r] [int] NULL ,
> [s] [varchar] (200) NULL ,
> [ImportFlag] [tinyint] NULL CONSTRAINT [DF_ImportStaging_ImportFlag]
> DEFAULT (0)
> )
>
> CREATE INDEX [IX_ImportStaging_e] ON [dbo].[ImportStaging]([e], [f])
>
> i can also say that the column tuple (e,f) _should_ be unique. My first
> thought was:
>
> UPDATE ImportStaging SET ImportFlag = 1
> WHERE EXISTS (
> SELECT TOP 500 *
> FROM ImportStaging i2
> WHERE i2.e = ImportStaging.e
> AND i2.f = ImportStaging.f)
>
> ...but seems like it won't work - i have a feeling that the exists is
> satisfied when i don't want it to be (i.e. always)
>
> So i'm fiddling with something like:
>
> UPDATE ImportStaging SET ImportFlag = 1
> FROM ImportStaging
> INNER JOIN (
> SELECT TOP 500 e,f
> FROM ImportStaging) dervivedTable
> ON ImportStaging.e = derivedTable.e
> AND ImportStaging.f = derivedTable.f
>
> Does that seem reasonable? (The update statement...not the scenario i've set
> up).
>
> Or perhaps someone can think of an IN or EXISTS version, rather than a join.

Why don't you have any primary key and why don't you use normal names
for your columns? Also you didn't specify the version that you are
using. If you are using SQL Server 2000, then just use the set
rowcount statement. If you use a newer version, you can use UPDATE
TOP (500) instead.

Adi
From: Roy Harvey (SQL Server MVP) on 3 Jul 2008 18:01
You are already close.

UPDATE TOP (500) ImportStaging
SET ImportFlag = 1

Roy Harvey
Beacon Falls, CT

On Thu, 3 Jul 2008 17:14:59 -0400, "Ian Boyd"
<ian.msnews010(a)avatopia.com> wrote:

>i want to flag 500 rows in a staging table as the rows i'm going to process
>right now. Once they've been processed they will be deleted, and another 500
>will get marked. i don't really care which 500 rows, just 500 of them. If
>the rows has a unique key, i would do an
> UPDATE ImportStaging SET ImportFlag = 1
> WHERE UniqueKeyColumnName IN (
> SELECT TOP 500 UniqueKeyColumnName
> FROM ImoprtStaging)
>
>But since there's no unique key column, i cannot do that. Also, i don't
>want to use the depricated
> SET ROWCOUNT = 500
>
>(unless you want to put $100,000 into escrow to cover the costs of updating
>the import process to not use ROWCOUNT when it finally goes away)
>
>
>
>The staging table DDL is as follows. Note the last column "ImportFlag" which
>i want to set to "1" for 500 arbitrary rows:
>
>CREATE TABLE ImportStaging (
> [a] [uniqueidentifier] NOT NULL ,
> [b] [uniqueidentifier] NULL ,
> [c] [uniqueidentifier] NULL ,
> [d [tinyint] NOT NULL CONSTRAINT [DF_ImportStaging_d] DEFAULT (0),
> [e] [int] NULL ,
> [f] [varchar] (50) NULL ,
> [g] [varchar] (100) NOT NULL ,
> [h] [varchar] (100) NOT NULL ,
> [i] [varchar] (250) NULL ,
> [j] [varchar] (100) NULL ,
> [k] [varchar] (50) NULL ,
> [l] [varchar] (50) NULL ,
> [m] [varchar] (100) NULL ,
> [n] [varchar] (50) NULL ,
> [o] [datetime] NULL ,
> [p] [varchar] (200) NULL ,
> [q] [int] NULL ,
> [r] [int] NULL ,
> [s] [varchar] (200) NULL ,
> [ImportFlag] [tinyint] NULL CONSTRAINT [DF_ImportStaging_ImportFlag]
>DEFAULT (0)
>)
>
>CREATE INDEX [IX_ImportStaging_e] ON [dbo].[ImportStaging]([e], [f])
>
>
>i can also say that the column tuple (e,f) _should_ be unique. My first
>thought was:
>
>UPDATE ImportStaging SET ImportFlag = 1
>WHERE EXISTS (
> SELECT TOP 500 *
> FROM ImportStaging i2
> WHERE i2.e = ImportStaging.e
> AND i2.f = ImportStaging.f)
>
>...but seems like it won't work - i have a feeling that the exists is
>satisfied when i don't want it to be (i.e. always)
>
>So i'm fiddling with something like:
>
>UPDATE ImportStaging SET ImportFlag = 1
>FROM ImportStaging
> INNER JOIN (
> SELECT TOP 500 e,f
> FROM ImportStaging) dervivedTable
> ON ImportStaging.e = derivedTable.e
> AND ImportStaging.f = derivedTable.f
>
>Does that seem reasonable? (The update statement...not the scenario i've set
>up).
>
>Or perhaps someone can think of an IN or EXISTS version, rather than a join.
>
From: Plamen Ratchev on 3 Jul 2008 18:04
On SQL Server 2005 you can use the MS specific UPDATE TOP syntax:

UPDATE TOP(500) ImportStaging
SET ImportFlag = 1;

Also, SET ROWCOUNT will still work in SQL Server 2008 and will be
depreciated in future version:
http://technet.microsoft.com/en-us/library/ms188774(SQL.100).aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Ian Boyd on 3 Jul 2008 19:45
> Why don't you have any primary key

a) there is no natural key
b) It's not my table

> and why don't you use normal names
a) column names obfuscated to protect the innocent
b) column names obfuscated to avoid people focusing on things are not the
question

> Also you didn't specify the version that you are using.

i was hoping for something fairly standard on SQL Server, that would be
usable for anyone stumbling across this post 15 years from now.

But let's assume it has to run on 2000 and 2005 - since it does.

 |  Next  |  Last
Pages: 1 2 3
Prev: Automation Script
Next: ANN: DBTyP.NET 2008 - Database Comparison Tool - New Release