What is wrong in this query am getting incorrect syntax near ','.error. I am using sql server 2014
DELETE ab, b
FROM Authors AS a INNER JOIN AuthorArticle AS ab ON a.AuthID=ab.AuthID
INNER JOIN Articles AS b ON ab.ArticleID=b.ArticleID
WHERE AuthorLastName='Tom';
DELETE FROM ObPostEvent WHERE EpisodeID IN (SELECT ID FROM ObMotherEpisode WHERE ID = @Urno)
DELETE FROM ObPostEvent INNER JOIN ObMotherEpisode ON ObMotherEpisode.ID = ObPostEvent.EpisodeID WHERE ObMotherEpisode.ID = @Urno
Are the 2 statements give the same meaning? I get an error for the 2nd statement as follows :-
Server: Msg 156, Level 15, State 1, Procedure spArchiveObPostEvent, Line 78
Incorrect syntax near the keyword 'INNER'.
Asking for some sample SQL/SP code to perform updates/deletes with joinedtables.Example 1:For every record that is matched on both tables A and B, update a field intable A.Example 2:For every record that is matched on both tables A and B, delete record intable A.TIA.~ Duane Phillips.
View 2 Replies View RelatedI have tried all possible combinations of changing this. But was not able to make the results tally.I am giving you a part of the query, there are others queries involving 4 tables which are based on this same temporary table query.
SELECT c.juris_id, b.jrnl_mo_yr
FROM a_trueup a,
#t_mths b, r_rj c
WHERE a.rlzd_mo_yr
=* b.jrnl_mo_yr
AND
a.juris_id
=* c.juris_id
[code]....
I tried using left outer join as mentioned in blogs but got a different result (14 rows).I also used set null off/on options but no luck ..
I have 3 tables.
Table 1:
ID Name Description
1 ABc xyz
2 ABC XYZ
Table 2:
RoleID Role
1 Admin
2 QA
Table 3:
ID RoleID Time
1 1 09:14
2 1 09:15
1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
I have 5 tables that are joined respectively,
Each one of the tables listed below has a “CreateDateTime” and “UpdateDateTime” fields, I need to get yesterday changes, I can get any record where either CreateDateTime or UpdateDateTime is greater than midnight yesterday butI need to watch dates on all of the tables so I need to do atleast 10 date checks.
If any table shows an updated or created record, I need to gather ALL of the information for that customer. So, if my name didn’t change (SCUS table), but my email does (SEML table), I have to pull out both the SCUS and SEML tables (and the others, of course). So It may not be simple WHERE clause, How can I achieve this:
SELECT
SCUS.CUSFULLNAME
,
SCUS.CUSMIDDLENM
,
SCUS.CUSLASTNM ,
[Code] ....
create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4) ,(1,1,2,2015,3);
create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');
I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this
that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked
I want to update above information means unchecked alredy check data should delete and new check data add , already check data update
I have use merge statement but problem is that at single time only one row execute that is check because data come from data gridview.
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I am using SQL Server 2012 Standard Edition. I have a requirement to instantaneously move data from 3 tables that are dependent on each other in a database to another database to same tables with same structure and dependencies.
I can set up replication to manage this. However the data once moved over has to be deleted from the source database immediately after the move. Hence replication is ruled out. Also data is continuously being insert into source database into those 3 tables.
I want to create a sql agent job that handles the data move and delete process and shcedule it to run once every minute. What is a best strategy to handle this without causing deadlocks in the source database?Below is the ddl and all objects in source database match the destination database the only difference is destination has 100 tables and source has only 3 tables which are shown below
CREATE TABLE [dbo].[StackPosition](
[StackPositionId] [uniqueidentifier] NOT NULL,
[AccountTriggerId] [uniqueidentifier] NOT NULL,
[StackPositionStatusId] [int] NOT NULL,
[QueuedAt] [datetime] NOT NULL,
[LastUpdatedAt] [datetime] NULL,
[Code] ....
I have a table named [New Item] and created a default constraint on a column, and i wanted to change the data type of the column using the query
alter table [new item]
alter column pcs_qty decimal(15,2) not null
but the name of the default constraint is 'DF__New Item__Pcs_Qt__2D12A970' and i am not able to delete the constraint because it contains a space in between.Is there any work around for this.
I tried to delete the constraint by using the query
alter table [new item]
drop constraint 'DF__New Item__Pcs_Qt__2D12A970'
but I am getting the exception,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DF__New Item__Pcs_Qt__2D12A970'.
I have a table with more than 1000 records. The columns look like this
Import id | Reference | Date | Username | Filename
Some of the rows are duplicates except for the fact that they have different values for the first column alone (import id)
11111 test 21-07-2015 xxxxxx yyyyyy
22222 test 21-07-2015 xxxxxx yyyyyy
Is it possible to delete these kind of duplicate rows by ignoring the import id ?
I'm trying to delete the selected data from a table colum from a select statement.
This is the select statement
SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification
& invitation%')
What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
I am working on an app that getting quite a few deadlocks due to delete statements. I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.
Open this url in new tab [URL] ....
I have a table which has a recursive relationship to its self neither of the columns involved are FK columns
CREATE TABLE [dbo].[ix_Trace](
[Trace_Id] [uniqueidentifier] NOT NULL,
[Trace_BubbledTraceEnter_Id] [uniqueidentifier] NULL,
[Trace_EnterId] [uniqueidentifier] NULL,
[Trace_Application_Id] [uniqueidentifier] NULL,
[Trace_Session_Id] [uniqueidentifier] NULL,
....
Where Trace_EnterId is the parent ID and BubbledTraceEnter_Id is the child ID. My test case has nested trace lines that go down 5 levels, but when I delete the top trace line, only the child directly under it is deleted leaving the other 3 as orphans. Here's the trigger:
ALTER TRIGGER [dbo].[DTrig_xTrace]
ON [dbo].[ix_Trace]
FOR Delete
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM ix_Trace
FROM ix_Trace INNER JOIN
deleted as dt ON ix_Trace.Trace_BubbledTraceEnter_Id = dt.Trace_EnterId
How can I make this cascade all the way down?
I've a table named Master in which a column is referenced in other tables like Child1, Child2,.. ChildN. I've deleted a part of data( say Column Id values 1,2,3,4,5) from all the Child tables which pointed to Master table Id column.
Now, I want to delete the same Id values from Master table as there rows are not referred in any of the child tables. When I try to delete the Id values 1 thru 5 from Master table, it is scanning all child tables for the references and taking lot of time for the deletion.
Is there any way to specify to the system(in the query) to delete the Master table values without scanning the child tables..?
I just wanted to delete a record from a filetable by "delete from dbo.DocumentStore where stream_id = 'A322276D-AE65-E511-8266-005056C00008'".
Then i received error 1934:
Meldung 1934, Ebene 16, Status 1, Zeile 578
Fehler bei DELETE, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ANSI_PADDING'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierte Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen
und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind.
Something that i made a mistake with the SET-option for 'ANSI_PADDING' with indexted views and/or calculated rows and/or filtered indeces ...
I've been able to get this select query to work, but I'm not sure how to modify it to turn it into a DELETE query:
USE QSCTestENG
select p.[testid], COUNT(c.[testid])
FROM [dbo].[tblTestHeader] p
left outer join [dbo].[tblTestMeasurements] c ON p.[testid]=c.[testid]
where p.[model] = 'XPPowerCLC125US12'
group by p.[testid]
having COUNT(c.[testid]) <>48;
I have a large table with 100 Million records that has around 1 million duplicate records that need to be deleted.
I am running a script that creates a staging table called,DuplicateTable that collects all the duplicates and then I want to write a an effecient delete statement.
Is it possible to write something like:
delete from OrigTable O join DuplicateTable D
on O.Key = D.key
Or do I have to run a loop on the DuplicateTable and run a delete statement record by record ?
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
I would like to perform autodeletion operation from my table using stored procedure....
Here is my table structure:
Emp.id | emp_name | dept | desig | time_out | date | emp_sign |
if the user inserts new employee record that record should delete automatically from original after
30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...
How to delete records from multiple tables if main table’s entity is deleted as constraints is applied on all..There is this main table called Organization or TblOrganization.and this organization have branches which are in Brach table called tblBranch and this branch have multiple applications let say tblApplication and these application are used by multiple users called tblUsers.What I want is: when I delete the Organization All branches, application and users related to it must be deleted also.How I can apply that on a button click in asp.net web forms..Right now this is my delete function which is very simple
Public void Delete(int? id){
var str=”DELETE FROM tblOrganization WHERE organizationId=”+ id ;
}
And My tables LOOK LIKE this
CREATE TABLE tblOrganization
(
OrganizationId int,
OrganizationName varchar(255)
[code]...
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies View RelatedI 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?
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
Hi,
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
Angel
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedHello:
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
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
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