Delete From A Table Using Sql

Jan 31, 2007

Good evening everyone,I have a problem with deleting a record in a table.

I have the table:

create table Employee(eid integer not null,
shop_id integer,
fname varchar(20),
lname varchar(20),
primary key (eid),
foreign key (shop_id) references Store);

create table Store(sid integer,
address varchar(20),
primary key (sid));

I have inserted many Employees and when i do select they work fine.
The problem is with Delete e.g. i want to delete the Employee with Eid = 12 i do:

Delete from Employee where shop_id = '12'

and it says

ERROR at line 1:
ORA-02292: integrity constraint (user....) violated - child
record found

What do i do wrong?

View 3 Replies

Soft Delete In Table, Why Merge Agent Report Hards Delete On Table ?

Feb 1, 2007

Hi seniors

there are two tables involve in replication let say table1 and replicated table is also rep.table1.

we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.

plz let me know where am i wrong and how i put it into right way.

there is no triggers on published tables and noother trigger is created on published table.

regards

Ahmad Drshen

View 6 Replies View Related

Copy And Delete Table With Foreign Key References(...,...) On Delete Cascade?

Oct 23, 2004

Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View 1 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

Allow Single Row Delete From A Table But Not Bulk Delete

Sep 16, 2013

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS

[code]...

When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

View 3 Replies View Related

Delete Table And Immediately Crate Table, Error Occur Table Already Exist

May 29, 2008

'****************************************************************************

Cmd.CommandText = "Drop Table Raj"

Cmd.ExecuteNonQuery()

Cmd.CommandText = "Select * Into Raj From XXX"

Cmd.ExecuteNonQuery()

'**************************************************************************

This generates error that Table already exist.

If Wait 1 sec then execute statement then it works fine.

Thanks in Advance

Piyush Verma

View 1 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

How To Alter The Table With Delete/update Cascade Without Recreating The Table

Jul 26, 2004

I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.

Thanks

************************************************** ******
Contract table DDL is

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

View 3 Replies View Related

HOW TO: Delete All Records From A Table Where A Child Record From Another Table Does Not Exist.

Mar 17, 2008

I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.

Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime

Child Table: TBL_VERIFICATION
PK: Serial Number

Thanks,
Sean

View 1 Replies View Related

Delete Rows In One Table By Referencing Another Table Info

Sep 16, 2004

I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.

Here is a basic breakdown of what I am trying to do:

Table1 (the table where the rows need to be deleted from)
Column_x (Holds the id that is unique to the various rows of data - User ID)

Table2 (Holds the user information & has the associated ID)
Column_z (holds the User ID)

I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.

Code:

SELECT Column_z
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z <> Column_x

This did not seem to do what I needed, it did not delete any rows at all.

I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2

Then I tried another scenerio that I also needed to do:

Code:

SELECT Column_z, Column_a
FROM dbo.Table2
DELETE FROM dbo.Table1
WHERE Column_z = Column_x AND Column_a='0'

'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.

Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.

Any ideas on what I am doing wrong here?

View 3 Replies View Related

SQL 2012 :: Delete From One Table Based On Results Of Other Table

May 28, 2015

I have this table:

with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select

[Code] ....

I want to make a delete statement like this:

select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae

But it seems that I cant delete like this.

View 5 Replies View Related

Blank Spaces In Table Name, Cannot Delete Table

Mar 10, 2000

A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.

Does anyone know how I can delete this table? Please let me know.

View 1 Replies View Related

Delete Or Drop Table Then Create Table

Mar 14, 2007

which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.

View 7 Replies View Related

Delete SQL Table Using A Variable That Refers To The Table Name

Jul 20, 2005

SQLLY challenged be gentle --Trying to create code that will drop a table using a variable as theTable Name.DECLARE @testname as char(50)SELECT @testname = 'CO_Line_of_Business_' +SUBSTRING(CAST(CD_LAST_EOM_DATEAS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),4)+ '_' + 'EOM'FROM TableNamePrint @testname = 'blah...blah...blah' (which is the actual tablename on the server)How can I use this variable (@testname) to drop the table? Undersevere time constraints so any help would be greatly appreciated.

View 2 Replies View Related

Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A

Mar 20, 2008

Hi thanks for looking at my question

Using sqlServer management studio 2005

My Tables are something like this:

--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]

-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]

--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE

DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39

Well this might look like a very basic error:
I get this Error after trying to delete something from Table ��Employee�?

The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.

Yes what I��ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a ��Branch�? entry, basically each entry in ��branch�? and ��Employee�? is child of each other which makes things more complicated.

My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use ��ON DELETE CASCADE�? or something?

Thanks

View 8 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

How To Delete From Table A Based On Table B

Nov 17, 2005

lets say i have 100 employees on Table A and I have 10 employees on Table B. I want to delete all the employees in Table A that are in Table B. Delete From TableA Where EmpNum = (Select EmpNum From TableB)The above SQL wont work but i am looking for something similar. any ideas?

View 1 Replies View Related

Need To Delete A Table Which Has A Reference To Another Table

Dec 12, 2007

Hi Friends...

In general we cannot delete a table if it refers another table. But in the process of backup and restore, we need to delete all the tables, so we have return an asp.net function...

Dim Datatable As String
Dim ds As New DataSet
ds = gDatabase.ExecuteQuery(" select Table_Name from Information_schema.Tables where Table_type='TABLE'")
If ds.Tables.Count > 0 Then
If ds.Tables(0).Rows.Count > 0 Then
Dim dr As DataRow
Dim da As SqlCeDataAdapter
Dim Cmd As String
For Each dr In ds.Tables(0).Rows
Datatable = dr.Item("Table_name")
Cmd = "Delete from [" & Datatable & "]"
gDatabase.ExecuteNonQuery(Cmd)
Next
End If
End If

we got an error like

"Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Electricalworks' because it is being referenced by a FOREIGN KEY constraint.
"

how to override these errors, if we have to delete all the tables. We have about 200 tables.

View 7 Replies View Related

Delete Record From Table A That Is Not In Table B

Apr 2, 2008

I have two tables;
Table A
id, name
101, jones
102, smith
103, williams
104, johnson
105, brown
106, green
107, anderson

Table B
id, name, city, state

101, jones, des moine, Idaho
103, williams, Corvallis, Oregon
104, johnson, Grand Forks, North Dakota
105, brown, Phoenix, Arizona
107, anderson, New York, New York

I need to delete records from Table A that are not in Table B. My front end is writen in .net and I am using Data Access Layer along with a Business Logic Layer for data interaction.

I have tried at least seven variations of joining, right outer join, left outer join resulting in wiping our the entire table or nothing at all; not to mention deleting the record that ought to remain and keeping the record that needs to be deleted!

In my BLL I tried to capture the rowsAffected for the deletion by using-without success.

Dim rowsAffected As Integer = Adapter.ID_Deletion(ID)

If rowsAffected = 1 Then

Exit Function

Else

Return rowsAffected = 1

End If

Please help.

MsMe.

View 9 Replies View Related

Delete More Than 1 Table

Jul 9, 2001

I need to delete approx 300 tables from a database. Does anyone know the best way to do this.. Thanking you in advance.

View 3 Replies View Related

Table Delete

Aug 6, 2002

A user table in a database has somehow been marked as a system table. Is there any way, short of dropping and rebuilding the database to remove this table?

Thanks,
Robert

View 1 Replies View Related

How To Delete Last Row In A Table

Sep 10, 2007

Hello - I inserted a row and now need to delete it. Is there a simple query to delete the last row inserted into a table?

Thanks for your help!

View 10 Replies View Related

4G Table's Delete Uses 20G Log???

Apr 3, 2008

I've a table with a size of ~4G and 6million rows in SQL server 2005.
Deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)

TIA,
DataDealer

View 3 Replies View Related

Cannot Delete A Row In A Table

Mar 25, 2007

I have a table in a SQLEXPRESS database that I read in C# app. It supposed to have only two rows but a number of fields with Float or Int type parameters. It is a tedious process to tune them all up in the app. The table is used for storage, naturally.

For some reason, I won't go into I ended up with having 3 rows in this table at this point. This third row is identical to the first one in the values of all fields. It happened because I deleted a column that made these rows not unique and after that the trouble began. Before deletion of that column I had 4 rows, then I deleted the 3rd one and row #4 moved in place of #3.

For the life of me I cannot delete this 4th row no matter what I do. It gives me an error:

No rows were deleted. Problem occured while deleting row1 (sic!, I am trying to delete row #3 in fact). The row values updated or deleted either do not make the row unique or they alter multiple rows (2 rows).

Good grief! This is why I am trying to delete this row!

It does not allow me to edit the fields to change the values to make them not unique. The same error pops up. It does not allow me to change the values of the first row which is identical to the third one.

It is not the first time this happened. I had it once or twice before. Back then I had to truncate the table and enter new values from scratch I am kind of tired of doing it.

What is the way to handle this strange behavior?

Thanks.

View 3 Replies View Related

How To Know Whom Delete Table Or Sp

Jan 31, 2008

Hi

Somebody delete my table in sql server. How can i identify that (who delete this, datetime, etc). Is there any way in sql server 2000 / 2005

Thanks in advance

View 3 Replies View Related

How Do I Delete When Using A Linking Table

Aug 12, 2007

Can someone help me with how I should set the relationship in my address tables and how they will affect the deleting of a client?
So, if I have:
tblClients ClientID
tblClientAddresses AddressID
tblClientAddressLinks AddressID, ClientID
Currently I have them set as a 1-M from tblClients to tblClientAddressLinks and a 1-M from tblClientAddresses to tblClientAddressLinks
If I try and delete a client and there address is shared wont this go very wrong?
So here is what happens when I try and delete a client:
The DELETE statement conflicted with the REFERENCE constraint "FK_tblClientAddressLinks_tblClients". The conflict occurred in database "DBSQL2", table "dbo.tblcLIENTAddressLinks", column 'clientID'.The statement has been terminated.
Here is what happens when I try and delete an address:
The DELETE statement conflicted with the REFERENCE constraint "FK_tblClientAddressLinks_tblClientAddresses". The conflict occurred in database "DBSQL2", table "dbo.tblClientAddressLinks", column 'Address_ID'.The statement has been terminated.

View 3 Replies View Related

Delete A Table With Foreign Key

Aug 25, 2007

Hi how do i to delete a table with a foreign key?
When i try to delete the table i get error 3726

any?

View 3 Replies View Related

Restrict Delete From A Table

Jan 21, 2005

Hi

I need to restrict delete from one table by any user of SQL. How can we do this? This is our master table and we dont want any one to delete data from this table.

Thanks
Bala

View 1 Replies View Related

Delete Oldest Table

Jul 4, 2001

Hi,
I have a database that stores monthly nameed tables for historical data, but after a time (could be 1,2,3 months or on request) I need to be able to delete the oldest table. The tbale name format is ?????_mm_yyyy. Is there a MIN statement that could work on the create date?
Any T-SQL statements that would help me on my way would be great
Thanka!

View 4 Replies View Related

Unable To Delete A Row In A Table

Jul 13, 2001

Is there any way to delete an unwanted row on a table with 300 or more constraints with less modifications to contraints? Or is there any other way that can remove the unwanted row without much risk? Dropping the constraints is possible but re-adding the constraints may not be possible because the create constraint statements are not known. And readding the constraints may take time because the database physical size is around 1GB... Please help

View 2 Replies View Related

SQL Delete Table Data

Aug 8, 2000

How can I erase all data from every table in a SQL Server 7.0 database and leave all constraints and relationships in tact? I'm wanting to have just the structure or frame work with no data in any table. There are over 130 tables so I need to automate this. Any Suggestions?

View 3 Replies View Related

How To Delete The Records In The Table

Jul 20, 2005

:confused: Urgent!!
Hi there. I use MS SQL server. I would like to separate the data from one table to two tables refer to two reference tables and the following conditions:

Let say these two reference tables are called:
Table A & Table B

Group A:
1. Same date in Table A & Table B
2. Same ID in Table A & Table B (ID is not unique)
3. Same name in Table A & Table B (Name is not unique)

Combine all of these three conditions for unique identifier.

I used the following SQL code to separate the required data that match the above conditions to the new table.
(Code)
select a.Project, a.Site, a.S_number, a.Field_ID, a.Method, a.Analyte, a.Result, a.Units,
a.Qualifier, a.Dilution_Factor, a.Reporting_limit, a.Recovery_, a.Matrix, a.CAS_Number, a.Sample_Date,
a.Received_Date, a.Prep_Date, a.Analysis_Date, a.Batch_ID, a.Data_Package_num_SDG, a.Lab_Sample_ID,
a.Lab into APPL_union_exist from APPL_union_update a, Before_01012004_report b
where a.Field_ID = b.[Field Sample] and a.Sample_Date = b.Collected and a.Analyte = b.Analyte

However, I don't know how to delete the data that copied to the new table in original table, or separate that to the new table. Wish someone can help me. Thanks a lot

View 1 Replies View Related