I don't understand how this works, however it works (sometimes)

I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.

Does anyone see the error?

Thanks

--=========================================
--Create a local temporary table that hold
--all the data from the source table
--=========================================

SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC

--=========================================
--Remove table entries that are no longer
--needed or that have to be updated
--=========================================

DELETE FROM LTTSTOCK

WHERE NOT EXISTS( SELECT * FROM #TEMP

WHERE LTTSTOCK.WarehouseNo = LTWHLO

AND LTTSTOCK.Location = LTWHSL

AND LTTSTOCK.ItemNo = LTITNO

AND LTTSTOCK.NumberAvail = LTAVAL

)

--=========================================

--Insert data that is missing or that

--needed to be updated and was previously

--deleted

--=========================================

INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

Walter:

You have the delete statement:

DELETE FROM LTTSTOCK
WHERE NOT EXISTS( SELECT * FROM #TEMP
WHERE LTTSTOCK.WarehouseNo = LTWHLO
AND LTTSTOCK.Location = LTWHSL
AND LTTSTOCK.ItemNo = LTITNO
AND LTTSTOCK.NumberAvail = LTAVAL
)

Does the the combination of (1) ItemNo, (2) WarehouseNo, and (3) Location guarantee uniqueness in your table?

Dave

|||

Hi Dave

Yes this is the case

Walter

I don't suppose BULK UPDATE exists?... like BULK INSERT?

I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.

Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?

Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!

You'll need to bulk insert into a work table, then do the update.

Unfortunately (or maybe, fortunately ) there's not Bulk Update.

Or, you could do the processing with SSIS....

I don't believe ...

I don't believe ...that not exists a resource for put optional parameters for filter in Reports else WorkArounds !!!

Specify a default parameter value. You can then use expressions which allow a row to pass through the filter if this default value is selected.|||

No,

I can't default values

If user not select anything on the combo Box in the parameter filter my reports have to show all rows

I would like to do this whitout use WorkArounds !

[]'s

Christian

|||

This works if you are using Procedure for data retrival and Query parameters to fill the combo box.

If you are using Query Parameters to fill the combobox, then add 'All' as a parameter using UNION function to let user know that you are selecting everything. In the stored procedure ignore the parameter if the user is passing 'All' as a parameter and select everything.

Hope this helps.

|||

Thanks Veepak

This Really work but I think yet this is a workaround !!
I don't have the same performance when I put UNION function in my Query.

But, thanks very much !

|||

Please can you mark it as an answer , it helps if anybody has similar question.

sql

I can't work out. Duplicate row

Dear all,
I am not be able to delete a row in the following table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSysRights]
GO
CREATE TABLE [dbo].[tblSysRights] (
[sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
[sinUserID] [smallint] NOT NULL ,
[sinApplicationID] [smallint] NOT NULL ,
[sinType] [smallint] NULL
) ON [PRIMARY]
GO
No indexes. No constraints. No relationships. What on earth hell is happenin
g?
SELECT *
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
725 61 163 null
725 61 163 null
I obtain a warning which tell me:
"key column information is insufficient. too many rows affected"
Thanks in advance and best regards,Set RowCount 1
Delete
FROM tblSysRights
WHERE (sinUserID = 61) AND (sinApplicationID = 163)
Set RowCount 0
Madhivanan|||Enric,

> I am not be able to delete a row in the following table:
Can you be more specific?
From where and how are you trying delete the row?
AMB
"Enric" wrote:

> Dear all,
> I am not be able to delete a row in the following table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblSysRights]
> GO
> CREATE TABLE [dbo].[tblSysRights] (
> [sinRightID] [smallint] IDENTITY (1, 1) NOT NULL ,
> [sinUserID] [smallint] NOT NULL ,
> [sinApplicationID] [smallint] NOT NULL ,
> [sinType] [smallint] NULL
> ) ON [PRIMARY]
> GO
> No indexes. No constraints. No relationships. What on earth hell is happen
ing?
> SELECT *
> FROM tblSysRights
> WHERE (sinUserID = 61) AND (sinApplicationID = 163)
> 725 61 163 null
> 725 61 163 null
> I obtain a warning which tell me:
> "key column information is insufficient. too many rows affected"
> Thanks in advance and best regards,
>
>|||Thanks for that Madhinavan.
Done successfully.
Thanks Alejandro, too.
Regards,
"Alejandro Mesa" wrote:
> Enric,
>
> Can you be more specific?
> From where and how are you trying delete the row?
>
> AMB
>
> "Enric" wrote:
>|||Just to add a couple of points. For elimination of duplicates, refer to:
http://support.microsoft.com/defaul...b;EN-US;q139444
Use keys in all your tables, and you will avoid a lot of such headaches. On
a related note, a couple of comments:
* You seem to have an identity column which is constrained to be unique, but
apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
Check where the "hole" is.
* Your naming conventions are stale which can be better -- search for some
good guidelines in the archives of this newsgroup.
* Use Query Analyzer for testing your queries, the EM query interface is
buggy and has certain limitations.
* And most importantly, use primary keys in your tables. In relational
database, a value is identified using <table, column, key> triplet. With
duplicates, such identification becomes impossible and relational operations
cannot be used beneficially. The impossibility of logically deleting one row
out of duplicates is often cited as a prime illustration of the consequence
of allowing duplicates by SQL. ( SQL language inventors and its flag bearers
should take the credit, for even standardizing them ! )
Anith|||Thanks for that Anith.
That table was defined without identity fields or key. Just a mess-up.
Best Regards,
"Anith Sen" wrote:

> Just to add a couple of points. For elimination of duplicates, refer to:
> http://support.microsoft.com/defaul...b;EN-US;q139444
> Use keys in all your tables, and you will avoid a lot of such headaches. O
n
> a related note, a couple of comments:
> * You seem to have an identity column which is constrained to be unique, b
ut
> apparently it seems to be violated with SET IDENTITY_INSERT ON setting.
> Check where the "hole" is.
> * Your naming conventions are stale which can be better -- search for some
> good guidelines in the archives of this newsgroup.
> * Use Query Analyzer for testing your queries, the EM query interface is
> buggy and has certain limitations.
> * And most importantly, use primary keys in your tables. In relational
> database, a value is identified using <table, column, key> triplet. With
> duplicates, such identification becomes impossible and relational operatio
ns
> cannot be used beneficially. The impossibility of logically deleting one r
ow
> out of duplicates is often cited as a prime illustration of the consequenc
e
> of allowing duplicates by SQL. ( SQL language inventors and its flag beare
rs
> should take the credit, for even standardizing them ! )
> --
> Anith
>
>