Dear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!
View 6 Replies View RelatedCan I delete the record if it exist before we do an insert at the DataFlow level base on a key of the record we are working on? Basically we want to keep history records and delete and reinsert any records that exist in the table.
View 5 Replies View Relatedhelp on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B
Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist
select
empID,ShiftDate,shiftType
from
table_A
table_A
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2
TABLE B
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
TNX for the help
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
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
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View 1 Replies View RelatedIs that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
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
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View RelatedACADEMIC_YEAR COURSE_REFERENCE TERM
------------- ---------------- --------
2014 -1465586182 TERM4
2014 -772484485 TERM4
With the above data, user must check and delete the row only if exist
I have query some thing like this:
DELETE FROM epas_publish_course where COURSE_REFERENCE IN ( SELECT * FROM (SELECT COURSE_REFERENCE FROM epas_publish_course WHERE COURSE_REFERENCE) AS X) ;
which deleting the complete table but i need to delete one particular row...
hi,
I would like asked an opinion about the system of my coding if is ok? I have one table here to modify. My point here is to count the record to avoid a duplicate record .
rs.open "Select count (field) as name from table Where field='001'",cn
If rs!name > 0 then
Update sqlstatement
Else
Insert sqlstatement
Endif
Any suggestion pls..
Hi all,
I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like
Procedure [dbo].[CleanResults]
@RecsToKeep bigInt,
@Output Int output
as
Declare @Date Datetime
Declare @Count bigint
set @Date = getdate()
Select @Count = Count(UniqueID) from [Main]
Print 'THE COUNT IS'
print @Count
if ( @Count > @RecsToKeep) begin
set @Count= @Count - @RecsToKeep
Print 'THE Number to delete is'
print @Count
select TOP(@Count) UniqueID from Main order By [Main].[TestDateTime] desc
Delete from [Main] where exists (select TOP(@Count) * from Main order By [Main].[TestDateTime] desc);
set @Output = @Count
end
else begin
set @Output = -1
end
whats odd is that the select staement will evaluate correctly and return the oldest record @Count record, however the delete stament removes all the records. An advice would be appriciated.
Thanks Christopher
PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
Hello,
I need to delete rows from one table that appear in another table.
For example:
newnames has fname, lname, age
oldnames has fname, lname, age
So, I want to be able to remove the rows from newname where there are rows in oldnames. This should be simple enough.
This is what I'm trying to use but it is deleting everything from oldnames.
delete from oldname
select * from newnames
Hi all,
I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.
The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?
Am I correct in the way I am doing or there is something better to handle this?
Thanks in advance
Hi all:
for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi");
if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like :
if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database");
I can't imagine I got the bool variable via exception...
thanks to all..
Hello,few users have problems with our application(ADP/MsAcces2000+SqlServer2000). Whenthey want to open a report which source is a stored procedure, they getmessage that "record source specified..... do not exist" - of course,it's not true.More details:- other users don't have this problem- they have WinXP- all users have the same permissions to SQL objects- I've checked Microsoft KB(http://support.microsoft.com/kb/243532/en-us) and they write about"dbo." before source name - I use that, so it's not a solution. Whatmore, in application there are lists which have SP as row source andwithout "dbo." and it works. (In lists a row source is defined as:"exec <sp_name>", in reports as: "dbo.<sp_name>").- computers have all service packs, new version of MDAC, etc.- I asked another user, who doesn't have this problem on his machine,to login on "bad" machine and he couldn't open reports too.Maybe someone could help? because I don't have more ideas.
View 1 Replies View RelatedOk,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************
View 1 Replies View RelatedGurus,
I have two list boxes, user can move items back n forth, from second listbox I am inserting values into a table. So far everything is working fine.
Now I want to delete all the existing values from the table before inserting evertime..Please help me in this I dont know what to do.
thanks
kalloo
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
Consider this scenario.
I have two database in the sql server and consider that i have a query which has 4 tables inner joined.
When i execute the query in the database1 , the query is returning rows, But when i execute the same query in the database2, the query is not retuning rows . I know that the
no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge
list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be
appericated.
I have two SPs, call them Daily and Weekly. Weekly will always callDaily, but Daily can run on its own. I currently use a global temptable because certain things I do with it won't work with a local temptable or table variable.I have been trying to get code so that if the table already exists, itjust keeps going and uses it, but creates the temp table if it doesn'texist. Unfortunately, no matter how I try to do it, it always attemptsto create it, raising an error and breaking the code.create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))insert into ##load_file_log (contents) values ('test record')IF object_id('tempdb..##load_file_log') IS not NULLprint 'exists'ELSEcreate table ##load_file_log (id int identity(1,1),contentsvarchar(1000))select * from ##load_file_logdrop table ##load_file_logIf I change it to IS NULL, the same error occurs (Server: Msg 2714,Level 16, State 1, Line 7There is already an object named '##load_file_log' in the database.)I have found one way to do it, but it seems a bit...clunky.IF object_id('tempdb..##load_file_log') IS NULLexec ('create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))')I'll use that for now, but is there something I missed?Thanks.
View 4 Replies View RelatedCREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10
[Code] ....
'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates
So 'Team A' will now have 3 records
Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010
'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date
So 'Team B' will now have 3 records
Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040
As for 'Team C' we have values for all 3 dates, no inserts needed.
Hi,
I'm calculating matrix including totals in the database.
I would like to set a background color for all total cells.
For total cells with now dat in database I fail to put codiotional formating.
I use the following code:
=iif(Fields!Col1Desc.Value="Total", "#60759b", "Transparent")
For cells under "Total" columns that do not got any db value I got color "Transparent" and not "#60759b"
How do I recognize to which column or row a cell belong in a matrix even if it not a value that come from db but empty one?
for example, note the background color of the cell of Male, Total and 2006
I got that matrix:
Year
Gender
Employee Status
2006
2007
2008
Total
Male
Active
0
0
1
1
None Active
0
0
3
3
Total
0
7
4
11
Female
Active
6
5
5
16
None Active
5
3
7
15
Total
11
8
12
31
Instead of that:
Year
Gender
Employee Status
2006
2007
2008
Total
Male
Active
0
0
1
1
None Active
0
0
3
3
Total
0
7
4
11
Female
Active
6
5
5
16
None Active
5
3
7
15
Total
11
8
12
31
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1
Union
Select Distinct([favoritesport]) from table2
Union
Select Distinct([favoritesport]) from table3
etc etc etc
Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
I know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.
Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like
"You email address is XXX"
YYYYY not found
"You email address is ZZZZ"
I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]
/****** Select ALL if where an email address is present in the list ******/
SELECT EmailAddressID,EmailAddress
FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN
(
'ken0@adventure-works.com', --1
'terri0@adventure-works.com', --2
[Code] ....
-- Test to see if a single email address is present
IF EXISTS
(
SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN ('25rob0@adventure-works.com')
)
BEGIN
SELECT 'Email address is presnt'
[Code] ....
When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.
I accidently made a duplicate record and I am unable to delete it. Everytime I try to delete, it gives me an error message saying:
Insufficient oe incorrect key coloumn information; too many rows affected by the update.
I am unable to delete either records. Is there another way of accomplishing this task. Thanks for your help in advance.
Bud
Hello i am new to MS SQL. Just moved in from MS Access.
I used to program in VB6.
mY Question is when i attemp to delete a record the followin error message displays
"Key column information is insufficiant or incorrect.Too many rows were affected by the update."
I have put the as code for the 'DELETE Record' command button in VB6 as follows.
adomain.Recordset.Delete adAffectCurrent
Please help me to safley delete a record from the SQL database using VB6.
Hi,
How can i delete a record from a table . table contains approx 3 million data and when i execute a query for delete it give error message from transaction log space problem.
Is there any way to stop the log in transaction log and delete the record.
thanks