Tuesday, March 27, 2012
Delete Data from a Column
I would like to delete all the data from a column so I can then update it
with new data.
Thankshow do you mean delete from a column? set it to null? In which case you
want UPDATE tblA SET colA = null.
You can only really delete a row, not a column, that's just updating a
value.|||looks painfully simple.. but still... :)
update table1 set column1 = null
--
"Chris" wrote:
> Can someone help with the following
> I would like to delete all the data from a column so I can then update it
> with new data.
> Thanks|||Thanks Guys
I am really new to SQL (Tax Accountant Not A Programer)
"Omnibuzz" wrote:
> looks painfully simple.. but still... :)
> update table1 set column1 = null
> --
>
>
> "Chris" wrote:
>|||no problem.. have a great day.
--
"Chris" wrote:
> Thanks Guys
> I am really new to SQL (Tax Accountant Not A Programer)
> "Omnibuzz" wrote:
>
DELETE and UPDATE Trigger question
I have a Trigger on a table. Here is the code
ALTER TRIGGER [dbo].[OnOrderDelete]ON [dbo].[orders] AFTERDELETE,UPDATEAS BEGINSET NOCOUNT ON;DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;END
Actually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:
When orders table is updated, the
"SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;"
part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @.ids =(SELECT idfromUPDATED) ")
Is this right? And how can I part UPDATED and DELETED ?
Thanks
Artashes
Because an update is logically a delete and insert combined into one atomic transaction.
And just an FYI - your trigger will fail if you ever try to delete multiple rows with a single statement. You should try:
DELETE FROM files where OrderID IN (SELECT ID FROM deleted)
|||Thanks for information!
Your way of deleting seems to be more correct!
But you know, my query works!!
Is there any explanation?
|||
Motley has given the explanation. In SQL Server there is no actually 'UPDATE' as you may expect just some modification on a row, instead an UPDATE consists an INSERT followed by a DELETE, so that's why you felt that DELETED is treated as UPDATED.
|||Iori_Jay, about DELETE and INSERT everything is clear.
I mean why works my query
DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;
Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write
DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);
Try executing DELETE FROM files.
With your trigger, it will fail.
|||Motley, I don't understand you!
And what am I executing now?
Or you mean only just "DELETE FROM files;" (delete all rows in files?)
I don't know about all files, but there were situations, when my query deleted 3 rows!
Not within a single statement.
DELETE from files where fileid=1
DELETE from files where fileid=2
would work, but...
DELETE from files where fileid=1 or fileid=2
would fail.
|||Thanks for info!
But there is no statement like "DELETE from files where fileid=1 or fileid=2" in my query? Am I right?
|||
artashes:
Iori_Jay, about DELETE and INSERT everything is clear.
I mean why works my query
DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must writeDELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);
I can't understand why such query works even when you're trying to delete multiple rows--the subquery will return multiple rows and "SELECT @.ids =(SELECT idfrom DELETED)" command will fail as it is trying to assign multiple values to a single variable.However the following query will work as it assigns the last id from deleted table to the variable:
DECLARE @.idsint;
SELECT @.ids =idfrom DELETED;
DELETE FROM filesWHERE OrderId = @.ids;
|||
Delete and update
I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.
I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?
Any suggestions?
RoyAre you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Roy" <praish1998@.yahoo.comwrote in message
news:1173148692.840874.72780@.c51g2000cwc.googlegro ups.com...
Quote:
Originally Posted by
Hi All,
>
I have a Access front-end,the tables are from SQL server 2000 and
linked via ODBC using DSN.
I have a Main table and several related tables.The Main table has a
One -to- Many relationship with the related tables.
When I try to delete a record from the front-end,I get a error msg "
the table is locked,you and another user are attempting to delete/
update the same record".I am the single user and I am at a loss to
understand what the problem is.
>
I have to explain that I never had a problem when i was using Access
as the backend too.Is it because of linking?
>
Any suggestions?
>
Roy
>
Quote:
Originally Posted by
Are you are running the sql statement from Access?
Is the DSN a User DSN or System DSN?
What SQL statements does Profiler show as being attempted?
How are the relationships enforced (which ON DELETE option is in use)?
Thursday, March 22, 2012
Delet function not working on Gridview
I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.
I using the products table in Northwind to learn this stuff.
The code that I used is below:
<%
@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %><!
DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><
htmlxmlns="http://www.w3.org/1999/xhtml"><
headrunat="server"><title>Untitled Page</title></
head><
body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource></div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>
</
body></
html>Any help would be appreciated - thanx
The table has a foreign key constraint.
If a product is part of an order, you may not delete the product.
Otherwise, when someone tried to look at the order, they would get an error about a missing product.
|||Thanx for your help - The reason I'm using the Products table in Northwind is, "I tried building a table based on my clients original data (his music shows itinerary) but it would not allow me to click the Checkbox in the Wizard - "Generate additional INSERT, UPDATE, and DELETE statements" when configuring the db
Can you tell me how I can remove the foreign key constraints (or) how can I duplicate the table, rename it and then remove all foreign key constraints
thanx
|||Open up the Keys folder under the table.
The foreign keys have a prefix FK_. Right click one and select Modify.
When you built your original table, did you give it a primary key? If a table does not have a primary key, you will not be able to generate Insert, Delete and Update statements.,
|||thank you so much - The primary key issue was the problem and now I have had no problems adding, deleting and modyfing the table. I am having a problem with the formatting of a datetime field. It keeps returning the date as well as the time (12:00 AM). Could you point me in the right direction to take care of this. Do I need to change the field or can I deal with it by formatting the column in the GridView?
thanx for all of your help
|||
In the GridView date column set the DataFormatString to {0:d} or {0:D} or {0:yyyy-MM-dd} etc.
And set the column's HtmlEncode property to false.
|||
Thanx Steve - Everything seems to be working fine now. Except,...
When I try to put this little app on my server (www.discountasp.net) - I get errors
Server Error in '/dave' Application.
Configuration Error
Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message:Unrecognized attribute 'xmlns'.
Source Error:
Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>Line 12: <connectionStrings/>
Source File:E:\web\nyguitarsne\htdocs\dave\web.config Line:10
I must be missing something crucial about deployment?
|||
PS - the site is sitting here:
www.caterdata.com/dave
|||
It's hard to say without seeing the whole file but it looks like you have a closing connectionStrings tag without an opening tag. The fix below may help (or you could delete both tags)
Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>
new: <connectionStrings>Line 12: <connectionStrings/>|||
The reason I'm confused is that it is working fine on localhost.
Do I need to call the site Administrator and set something up that I'm not aware of?
|||
It looks like the web.config file on you local machine and the server are different.
Sometimes they should be. For instance, the datasource on a local machine may not exist on the server.
|||
Try making sure the server is setup top use ASP.NET 2.0 and not ASP.NET 1.0
I have seen this cause weird problems as you are having.
David
|||Try making sure the server is setup to use ASP.NET 2.0 and not ASP.NET 1.0
I have seen this cause weird problems as you are having.
David
sqlFriday, March 9, 2012
DEFINITIVE ANSWER PLEASE -- can you UPDATE ntext datatype field??
column.
My ntext field will exceed 8,000 characters (typically twice that size
-- but just a text string).
One article (I think from MicroSoft) said you could NOT use ntext in
an UPDATE statement, but I've seen examples from other people using
it...but don't know if it's related to the size/characters issue.
Is this true or not?
Thanks very much...KathyKathyB (KathyBurke40@.attbi.com) writes:
> One article (I think from MicroSoft) said you could NOT use ntext in
> an UPDATE statement, but I've seen examples from other people using
> it...but don't know if it's related to the size/characters issue.
Yes, you can update an ntext column directly in an UPDATE statement.
I dont think there is a limitation, but it may be unpractiable if
you have a string which is million characters long.
There is also UPDATETEXT which permits you change parts of an ntext
column, but this function is certainly more complex to use, so as long
as you can do it with plain UPDATE stay with it.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, that's what I wanted to hear!
I don't need to save a million, just up to 50,000!
Kathy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Wednesday, March 7, 2012
Deferred Updates (Singleton Updates)
set the trace flag 8207 like in SQL 2000, but this is not working.
Thanks,
Andrew
MCSA,MCDBA
Hi Andrew - I've just tried adding the trace flag and it is added OK as
confirmed by "dbcc tracestatus". Did you have a problem adding it or was the
problem later on?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Sunday, February 19, 2012
Default value in SP
I use SP to update value in myTable,
MyTable have a few columns (about 15) and I want update not all field.
Sometime I need update column 2,3,5 and next time 4,5,11.
If parametr is not defined leave current value
My question is how set as default value of parameter equal current value in
table?
My template of procedure (SP)
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int,
....
@.par15 int,
)
AS
Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
@.idRow
I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
MySP 1,,,,,...,45
Thx PawelRUPDATE MyTable SET col1 = COALESCE(@.par1, col1), ...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>|||One option can be to use NULL as the default value and use functions ISNULL
or COALESCE in the UPDATE statement.
alter procedure MySP
(
@.idRow int //it is row id and ever is define
@.par1 int,
@.par2 int = null,
@.par3 int = null,
@.par4 intl,
@.par5 int = null,
....
@.par15 int,
)
AS
set nocount on
Update MyTable
set col1=@.par1, col2=coalesce(@.par2, col2), col3=coalesce(@.par3, col3),
col5=coalesce(@.par5, col5), ... col15=@.par15 where idRow = @.idRow
return @.@.error
go
"PawelR" wrote:
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value i
n
> table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>
>|||let the default value be null, or some other non-applicable value. something
like this:
alter procedure MySP
(
@.idRow int
@.par1 int=null,
@.par2 int=null,
....
@.par15 int,
)
AS
Update MyTable set col1=coalesce(@.par1,col1),
col2=coalesce(@.par2,col2),
..
where idRow = @.idRow
dean
"PawelR" <pawelratajczak@.poczta.onet.pl> wrote in message
news:%23Q95ibzRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Hello group,
> I use SP to update value in myTable,
> MyTable have a few columns (about 15) and I want update not all field.
> Sometime I need update column 2,3,5 and next time 4,5,11.
> If parametr is not defined leave current value
> My question is how set as default value of parameter equal current value
> in table?
> My template of procedure (SP)
> alter procedure MySP
> (
> @.idRow int //it is row id and ever is define
> @.par1 int,
> @.par2 int,
> .....
> @.par15 int,
> )
> AS
> Update MyTable set col1=@.par1, col2=@.par2 ... col15=@.par15 where idRow =
> @.idRow
> I want call MySP: MySP 2,4,,,,,,,,...,29 or MySP 4,4,1,3,...,34,33,12 or
> MySP 1,,,,,...,45
>
> Thx PawelR
>
Friday, February 17, 2012
DEfault SQL Server 2000 lock modes
Hi everyone,
I wonder that SQL Server 2000 support the following lokc modes by default or not.
Shared (S)
Update (U)
Exclusive (X)
Hi,
Thanks for this reply, this is the reply that I expected.
Again thanks,
Best wishes,
Mert