I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.
thanks
Jason
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies View RelatedHi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
hi!
I have a big problem. If anyone can help.
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Is there any other way to do this?
can DBCC help to retrieve this info?
Please advise me how to do this.
Thanks in advance.
Vaibhav
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedHere is my question if anyone can help...
I have two tables
Table 1
EmpName
PolicyNumber
Table 2
EmpName
PolicyNumber
NewEmpName
I would like to update Table 1 with the data from Table 2. Here is my problem..Lets say that I have two records in Table 2 that have the same policyNumber but two different NewEmpNames, it only takes the first. In other words, a single policynumber can be moved to a New EmpName and then again later on to another NewEmpName adn even again if need be
Any help is greatly appreciated.
-Matt
Please help
I have table1 which has many unique ID numbers and table2 that has many records for each ID. some of the ID numbers in table1 have changed and I have created a translation table (table3) that links the old and new ID numbers.
What I need to do is some sort of update sql statement that updates all the records in table2 changing all the oldID numbers to the new ones using the translation table.
Table1 and table2 are not linked...can anyone help me with the sql statement
example
Table 1
IDNUM NAME
12345 Joe
12346 Mary
12347 David
Table2
IDNUM FIELD1
12345 hello
12345 goodbye
12346 hello
12347 goodbye
12346 hello
12346 goodbye
Table3
OLDID NEW ID
12345 54321
12347 74321
need to change the IDNUM in Table2 to 54321 where IDNUM = 12345 and same with 12347..Need to do this for many many IDs but not all.
Thanks very much
Good day to all, I am new here so i hope i am doing things correctly.
The Company i work for make coils of shaped wire and work a 6 - 6 shift pattern
I have a database that is updated from a data collection source (MS Access) at 06:00 every morning. This seems to be working ok, my problem is that most coils fit nicely into the 6 - 6 shift pattern, but some now and again drift over into the next shift. I have written a crystal report that picks up this data. at the moment the coils are put in the database as: [Coil Start Time], [Coil Finish Time], [Coil Start Weight], [Coil Finish Weight], etc.
I have written (been helped to write) a SQL statement that will do the following:
Step 1: If the Coil Finish time is greater than the shift end time, then set the shit end time to be coil end time and zero start and finish wheight.
Step 2: The original Coil record is duplicated and Coil Start time set to start time of shift, all other data left alone.
Example of code:
-->>
SELECT [Batch Name], [Batch Start], [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift], [Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch End]) < 18) OR
((DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18))
UNION ALL
SELECT [Batch Name], [Batch Start], DATEADD(hour, 17, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
[Works Order No]
FROM dbo.tblCoilData
WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18)
UNION ALL
SELECT [Batch Name], DATEADD(hour, 18, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
[Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE DATEPART(hour, [Batch Start]) >= 6 AND DATEPART(hour, [Batch Start]) < 18 AND (DATEPART(hour, [Batch End]) < 6 OR
DATEPART(hour, [Batch End]) >= 18)
UNION ALL
SELECT [Batch Name], [Batch Start], DATEADD(hour, 5, DATEADD(minute, 59, CONVERT(char(10), [Batch End], 101))), 0, 0, [Product], [Shift], [Operator ID],
[Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18
UNION ALL
SELECT [Batch Name], DATEADD(hour, 6, CONVERT(char(10), [Batch Start], 101)), [Batch End], [Coil Start Weight], [Coil Finish Weight], [Product], [Shift],
[Operator ID], [Works Order No]
FROM dbo.tblCoilData
WHERE (DATEPART(hour, [Batch Start]) < 6 OR
DATEPART(hour, [Batch Start]) >= 18) AND DATEPART(hour, [Batch End]) >= 6 AND DATEPART(hour, [Batch End]) < 18
<<--
I have 2 options now
option 1:
Leave this as a SQL View and report from this
option 2:
Insert updated records to the tblCoilData table so that the data in the table is permanent
I would prefer option 2 but am a bit of a nugget when it comes to writing update / insert statements, Could someone please help me with this
Thank you very kindly
Regards
Steve Dyson
I Have three tables
TASK
taskid, taskname, projectid, workid
1,,1,1
2,,1,2
3,,2,3
PROJECT
projectid, projectname
1, project1
2, project2
3, project3
WORK
workid, workname
1,work1
2,work2
3,work3
I need to do an update this way
Update the taskname as 'projectname' + '_' + 'workname' for any projectid.
projectname and workname coming from the projectid and workid in the task record
so the task table becomes
1,project1_work1,1,1
2,project1_work2,1,2
3,project2_work3,2,3
I can get all the records doing this
SELECT p.projectname+ ' ' + w.workname AS 'NEWNAME'
FROM task t
JOIN work w
ON t.workid = w.workid
JOIN project p
ON t.projectid = p.projectid
WHERE projectid = 2
how do i do an UPDATE?
any help is appreciated
I have this code
I want to update nilai = total where kd_bulan = 9 and nilai = 0 where kd_bulan = 12
I'm having a problem with the syntax for doing an update to the top 25 records in my table.... This is what i have...
UPDATE TOP (25) FROM ud402.jd_mcp_master SET comments = 'MONDAY 092313 ' WHERE QUEUE_NAME = 'JD_Testing' ORDER BY DATE_WORKED ;
hi,
i want to update the second row of the c column.can any one help me in this . this is the sample records.
a b c
001testNULL
001testNULL
005testNULL
Hi all,
I have confusing problem here. My table which have about 150.000 rows have problem with updates on 15 rows.
It's access application which connect to sql server 2005 database. Access gets error:
"ODBC --update on linked table 'MyTable2 failed"
If i try to update on sql it's take couple minutes to update.
Evrything else on that table is ok.
I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.
View 1 Replies View RelatedHow to use db-lib to update/insert database records without using SQLlanguage. I want to change the value of the data individually withoutplugging in the new values in the SQL language then execute it. Theperfect situation for me is loop through the retrieved records thenedit the values individually until EOF.Thanks,Carlo
View 1 Replies View RelatedHi everyone,
I'm fairly basic in SQL and I was wondering if it was possible to update the id field of a certain table in all records of that table. And to update the links to those ids in other tables.
Hope you understand and thanks in advance,
Tobias
update top (50) percent table1 set fields1 = 1
update top (50) percent table1 set fields1 = 2
like the subject, all or half? thanks
I'm using SQL Server 2000.
I have a table with data similar to this.... Multi field = 0 indicates current record, 1 indicates an old record
FWK NVQ Multi Key Start Date NVQ Date FWK Date
NULL NULL 0 123456 03/04/2006 NULL NULL
NULL NULL 1 123456 03/04/2005 01/09/2006 NULL
NULL NULL 0 234567 03/04/2006 NULL NULL
NULL NULL 1 234567 03/06/2005 04/10/2005 03/11/2005
NULL NULL 0 345678 03/04/2004 NULL NULL
NULL NULL 1 345678 03/07/2003 NULL 01/12/2003
NULL NULL 1 345678 03/08/2002 NULL NULL
NULL NULL 0 456789 30/09/2002 11/06/2003 NULL
NULL NULL 1 456789 29/08/2000 NULL NULL
NULL NULL 0 567890 30/09/2002 11/06/2003 11/06/2003
NULL NULL 1 567890 29/08/2000 30/05/2001 NULL
NULL NULL 0 678901 03/04/2006 01/09/2006 15/09/2006
NULL NULL 1 678901 30/03/2005 30/08/2005 15/08/2005
NULL NULL 0 789012 02/03/2000 03/09/2000 15/09/2000
NULL NULL 0 789013 30/06/2001 07/08/2001 14/08/2001
I need to update the table, setting the first two columns to the date of the old records... ie. I want the table to look like this...
FWK NVQ Multi Key Start Date NVQ Date FWK Date
NULL 01/09/2006 0 123456 03/04/2006 NULL NULL
NULL NULL 1 123456 03/04/2005 01/09/2006 NULL
03/11/2005 04/10/2005 0 234567 03/04/2006 NULL NULL
NULL NULL 1 234567 03/06/2005 04/10/2005 03/11/2005
01/12/2003 NULL 0 345678 03/04/2004 NULL NULL
NULL NULL 1 345678 03/07/2003 NULL 01/12/2003
NULL NULL 1 345678 03/08/2002 NULL NULL
NULL NULL 0 456789 30/09/2002 11/06/2003 NULL
NULL NULL 1 456789 29/08/2000 NULL NULL
NULL 30/05/2001 0 567890 30/09/2002 11/06/2003 11/06/2003
NULL NULL 1 567890 29/08/2000 30/05/2001 NULL
15/08/2005 30/08/2005 0 678901 03/04/2006 01/09/2006 15/09/2006
NULL NULL 1 678901 30/03/2005 30/08/2005 15/08/2005
NULL NULL 0 789012 02/03/2000 03/09/2000 15/09/2000
NULL NULL 0 789013 30/06/2001 07/08/2001 14/08/2001
Can anyone help me with this?
Jon
Code Snippet
DECLARE @TABLE_JR TABLE
([FWK] datetime,
[NVQ] datetime,
[Multi] Smallint,
[Key] varchar(10),
[Start Date] datetime,
[NVQ Date] datetime,
[FWK Date] datetime)
INSERT INTO @TABLE_JR VALUES (null,null,0,'123456','2006-04-03',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'123456','2005-04-03','2006-09-01',null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'234567','2006-04-03',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'234567','2005-06-03','2005-10-04','2005-11-03')
INSERT INTO @TABLE_JR VALUES (null,null,0,'345678','2004-04-03',null,null )
INSERT INTO @TABLE_JR VALUES (null,null,1,'345678','2003-07-03',null,'2003-12-01' )
INSERT INTO @TABLE_JR VALUES (null,null,1,'345678','2002-08-03',null,null )
INSERT INTO @TABLE_JR VALUES (null,null,0,'456789','2002-09-30','2003-06-11',null)
INSERT INTO @TABLE_JR VALUES (null,null,1,'456789','2000-08-29',null,null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'567890','2002-09-30','2003-06-11','2003-06-11')
INSERT INTO @TABLE_JR VALUES (null,null,1,'567890','2000-08-29','2001-05-30',null)
INSERT INTO @TABLE_JR VALUES (null,null,0,'678901','2006-04-03','2006-09-01','2006-09-15')
INSERT INTO @TABLE_JR VALUES (null,null,1,'678901','2005-03-30','2005-08-30','2005-08-15')
INSERT INTO @TABLE_JR VALUES (null,null,0,'789012','2000-03-02','2000-09-03','2000-09-15')
INSERT INTO @TABLE_JR VALUES (null,null,0,'789013','2001-06-30','2001-08-07','2001-08-14')
Hi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
View 4 Replies View RelatedI wrote a sproc which does four things:
1. It looks at an option master to see if the record exists before inserting a new one
2. If the record is not there it inserts the optino record
3. Once the record is inserted I have to run a CASE statement on the record to determine its level
4. Once the level is determined, the record needs to be updated with the correct level.
1-3 work fine (when run without the update command).
However, even though I set a criteria, UPDATE still updates and not the one records.
Any idea why? set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--CREATE PROCEDURE [dbo].[sp_AddNewOption_OptionMaster_WithLevel]
@BuilderIDint,
@OptionIDINT,
@CommunityID INT,
@PhaseID INT,
@SeriesID INT,
@PlanID INT,
@ElevationID INT,
@CurrentSalesPrice Smallmoney,
@LocalComments Nvarchar (500),
@RoomID int,
@Package bit,
@Active bit
AS
--check to see if the option record exists
IF EXISTS (SELECT 1 FROM optionmaster WHERE BuilderID = @BuilderID AND OptionID = @OptionID AND CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID)
BEGIN
SELECT ' This option already exists in your Option Master'
END
ELSE BEGIN
--if the option record option does not exist, insert it
INSERT INTO [OptionMaster] ([BuilderID], [OptionID], [CommunityID], [PhaseID], [SeriesID], [PlanID], [ElevationID], [CurrentSalesPrice], [LocalComments], [RoomID], [Package], [Active], [DateAdded], [DateAvailable], [SalesPriceEffective], [OptionLevel])
VALUES (@BuilderID, @OptionID, @CommunityID, @PhaseID, @SeriesID, @PlanID, @ElevationID, @CurrentSalesPrice, @LocalComments, @RoomID, @Package, @active, GETDATE(), GETDATE(), GETDATE(),'10' )
SELECT ' Added to Option Master Successfully'
END
--once the option record is inserted, case it to find the its level (1-9)
--update the record with the approciate level.
UPDATE Optionmaster
SET optionlevel (
SELECT CASE WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '9' WHEN CommunityID = '0' AND PhaseID = '0' AND
PlanID > '0' AND ElevationID = '0' THEN '8' WHEN CommunityID = '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '7' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID = '0' AND ElevationID = '0' THEN '6' WHEN CommunityID > '0' AND
PhaseID = '0' AND PlanID > '0' AND ElevationID = '0' THEN '5' WHEN CommunityID > '0' AND PhaseID = '0' AND PlanID > '0' AND
ElevationID > '0' THEN '4' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID = '0' AND ElevationID = '0' THEN '3' WHEN CommunityID > '0' AND
PhaseID > '0' AND PlanID > '0' AND ElevationID = '0' THEN '2' WHEN CommunityID > '0' AND PhaseID > '0' AND PlanID > '0' AND
ElevationID > '0' THEN '1'
END AS OptionLevel --provides the option level required to update the record
FROM optionmaster
WHERE (BuilderID= @BuilderID And OptionID = @OptionID and CommunityID = @CommunityID AND PhaseID = @PhaseID AND PlanID = @PlanID AND ElevationID = @ElevationID))
--even through I specify the above criteria, it upates all records.
I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.Diane
View 2 Replies View Relateddear experts,
The code written below doesnt report any error and doest serve the objective of updating the record in the database. Can you please suggest what can be the problem?
Public Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conLath As SqlConnection Dim comLath As SqlCommand Dim upcmd
conLath = New SqlConnection("Data Source=heybabe1;Initial Catalog=sexsg;User ID=sa;Password=password") conLath.Open()
upcmd = "Update Hist set sett_price=@s_p, post_close=@p_s, vol_oq=@v_oq where hist_id=@hid"
comLath = New SqlCommand(upcmd, conLath)
comLath.Parameters.Add("@s_p", Data.SqlDbType.Decimal, 8) comLath.Parameters.Add("@p_s", Data.SqlDbType.Decimal, 8) comLath.Parameters.Add("@v_oq", Data.SqlDbType.Int, 8) comLath.Parameters.Add("@hid", Data.SqlDbType.Int, 8)
comLath.Parameters("@s_p").Value = IIf(sett_price.Text = "", DBNull.Value, sett_price.Text) comLath.Parameters("@p_s").Value = IIf(post_close.Text = "", DBNull.Value, post_close.Text) comLath.Parameters("@v_oq").Value = IIf(vol_oq.Text = "", DBNull.Value, vol_oq.Text) comLath.Parameters("@hid").Value = Request.QueryString("hid")
Try
comLath.ExecuteNonQuery() Response.Redirect("admindata.aspx")
Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else ' Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out" Message.InnerHtml = ex.Number Message.Style("color") = "red" End If End Try comLath.Dispose() conLath.Close()
End Sub
I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?
View 1 Replies View RelatedI have a stored procedure that I give some parameters and it joins a couple of tables and returns the results. I would like to know how to update the records in one of the original tables before returning results.
CREATE Procedure dbo.DS_GetSomething(@someValue char(5)AS
Select table1.* ,table2.* FROM table1 left inner join table1.itemid on table2.itemid where table2.someValue = @someValue
The table has a field of IMPRESSIONS that I would like to do something like:
;UPDATE table1 set impressions=impressions + 1 where recordid=??
I don't know how to access the recordids I just got from the original select statement.
Thanks for any help
Greg
Hi there,
I was trying to update records of a recordset(ADODB.Recordset) returned from a stored procedure(SQL server 7.0) in ASP file, this stored procedure select records into a temporary table, so the records returned by the SP actually physically are in tempdb database rather than in the user database.
When executing Update statement , I got the error message:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'Mydatabase' ignored, referencing object in tempdb.
Any ideas/comments would be highly appreciated!
Dana Jian
dan_jian@hotmail.com
I am trying to combine 2 tables, one is an event table, the other is a system table.
I would like to update field system.transaction with the first event.transaction
Where event.account = system.account
and event.effdt > system.effdt
and event.effdt <= system.effdt + 30
It's not really a join so I'm not sure how to write it.
Thanks,
Doug
I want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. I am using MS SQL 2000 and below is the scenario.
table1 columns
ID
Name
table2 columns
ID
Message
Select a.Id, a.name, b.message
from table1 a, table2 b
where a.id =b.id
a.id a.name b.message
1 John Msg1
2 Steve Msg2
3 Scott Msg3
4 John NULL - update b.message to 'Msg1'
5 Steve NULL - update b.message to 'Msg2'
6 Scott NULL - update b.message to 'Msg3'
7 John NULL - update b.message to 'Msg1'
8 Steve NULL - update b.message to 'Msg2'
If i will update the record per name i am using the query below and i am pre-selecting all the existing names.
update table2 b
set b.message=(Select top 1 b.message
from table1 a, table2 b
where a.id =b.id
[Code] ...
How to update this in bulk without preselecting all the names?
I have the following code:
TRUNCATE TABLE [Temp_Export];
INSERT INTO [Temp_Export]
(
[Code]....
The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?
For a staging table
Table TimeRange
id binary(16),
startTime datetime,
endTime datetime,
isValid tinyint
There are two validation rules: starttime cannot be null, endTime cannot be null (assume that we cannot set the columns as NOT NULL).
We would like to OUTPUT an error record for each validation error for every record in the TimeRange table.
Would there be a single statement that could do this ? (ie. would UPDATE invalid record AND would OUTPUT two validation error records for a record that has startTime = NULL AND endTime = NULL)
Something like:
UPDATE TimeRange
SET isValid = 0
OUTPUT inserted.id,
CASE WHEN inserted.startTime is NULL THEN inserted.startTime
WHEN inserted.endTime is NULL THEN inserted.endTime
END -- Needs to handle the case where both startTime and endTime are invalid
INTO @InvalidRecords
FROM (a SELECT stmt that is a table with a record for each validation error)
MERGE does not have the functionality needed (inserting multiple records for every invalid record).
Have not had success using a UNION ALL, as there is an error updating derived tables.
Have not had success creating a JOIN statement.
I have a table like this one, with PurchaseID be the primary key.
Code:
PurchaseIDItemIDQtyDatePurchased
1105152010-01-12
210742012-11-30
[Code]...
But I can't figure out how to get the max DatePurchased of the same ItemID.
I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))
DECLARE @Records TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15), moddate date, opflag char(1))
INSERT INTO @Records
VALUES
('JON','SMITH','20000101','M','12345','20140101','I'),
('JON','SMITH','20000101','M','12345','20140201','I'),
[code]....
I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.
Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126
After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A
Problem is very simple
I want to update sum of a field from another table to first table
TABLE ONE:
==========
ItemID
QtyInStock
Table TWO:
==========
BatchID
ItemID
Qty
I want to Update the QtyInStock of First Table with Sum(Batch.Qty)
here is the query i am writing but giving error
UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)
appericiating anyones help in advance
FAZEEL AMJAD
Systems Engineer
Crystal Technologies