Transact SQL :: How To Update Table In Many To Many Relationship
Nov 30, 2015
I am having challenge to update the redemption table from the multiple card activation table. I want to update the redemption table with the activation date closest to the redeem date.
For example: Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.
Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.
How to modify the update query to select the right activation row accordingly?
Below is my partial code I used but it always pick the 20071223 date to update my redemption table.
CREATE TABLE #Card
(
[CardNumber] varchar(20)
,[ Date] int
,[ Year] int
,[ Month] int
,[ Period] int
)
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)' Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
ID Flag TestDate Value Comment 111 2 12/15/2014 7.5 null 222 2 Null 10 received
Matrix_Current table could have 1 or multiple rows as below.
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 111 2 02/23/2015 7.9 111 2 04/07/2015 6.8 222 1 null 8 test comment 1 222 3 null 9 test comment 2
When I run below update
UPDATE AM SET M.Flag = MC.Flag, M.TestDate = MC.TestDate, M.Value = MC.Value, M.comment = MC.Comment FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID Flag TestDate Value Comment 111 2 01/26/2015 7.9 222 1 Null 8 test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID Flag TestDate Value Comment 111 2 04/07/2015 6.8 222 3 Null 9 test comment 2
I have 2 tables which with one-many relation. Table B has three child records based on Table A. I want to update value in Table from one of the records in table B.
Right now I am thinking of creating 2 temp table importing values there but does not seem to work. Can someone please help thanks. Armoghan
I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :
insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type]) SELECT TOP 10 [Date] ,[Count] ,[Rule Type] FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE()) = 1 order by Date desc
When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].
[tbl_FG_Alert_Count_All_Report] table.
Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table. What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25) , @SearchCol VARCHAR(255) , @LogonIDToProcess VARCHAR(50) , @Matched CHAR , @Cycle INT = 1 IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
ID (int, identity) Name (nvarchar(255)) Block (nvarchar(50)) Street (nvarchar(255)) Floor (nvarchar(50)) Unit (nvarchar(50)) Address1 (nvarchar(255)) Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
I am dynamically creating a job using sql script and it does work fine(It creates the job and when it's done it gets deleted as it's created dynamically. so I won't be having any job history at all in the system.). I want to update an existing table if the jobs fails and I am not sure how I can do that using t-sql script. Is that possible?I have an idea but not sure whether it works. In the job create script, can I include the code for if the job fails then go to step 2 and update the table column with the error??? If so, how can I retrieve the error???
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package.
Now we are getting dead lock because we are using same table at a same time. Because we are updating different records.
I am trying to build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES]( [APPLICATIONS_ID] [varchar](10) NOT NULL, [POS] [decimal](10, 0) NOT NULL, [APPL_STATUS] [varchar](5) NULL, [APPL_STATUS_DATE] [datetime] NULL, [APPL_APPLICANT] [varchar](10) NULL) GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES]( [APPLICATIONS_ID] [varchar](10) NOT NULL, [POS] [decimal](10, 0) NOT NULL, [APPL_STATUS] [varchar](5) NULL, [STATUS_START_DATE] [datetime] NULL, [STATUS_END_DATE] [datetime] NULL, [APPL_APPLICANT] [varchar](10) NULL) GO
I have two table i want to update one table using other table.query given below
--table 1
create table #temp_P (P_id int null, p_name varchar(50), h_id int)
--table 2
which is to be updated,check the p_name from above table( like 1 has same name like 4 so 1 will replace by 4) create table #temp_t (t_id int null, p_id varchar(50), r_id int)
<----------I have 2 tables are: 'customers (parent)' and 'open_ac (child)'
<--------I have tried to insert and update data into sql database by using textboxes (don't use datagrid)
<--------My tables details are below
<-------this table uses for keeping user data
customers fields:
Column name type length
Description
cu_id
int 4 Primary key Identifiers
cu_fname
nvarchar 20 allow null first name
cu_lname
nvarchar 40 allow null last name
cu_nat
nvarchar
20 allow null nationality
cu_add
nvarchar
40 allow null address
cu_wplace
nvarchar
40 allow null workplace
cu_tel
nvarchar 10 allow null telephone
cu_fax
nvarchar 10 allow null fax
cu_email
nvarchar 10 allow null email
<----the open_ac uses for keeping register date/time of customers
open_ac fields:
Column name type length Description
cu_id
int 4 link key
op_date date/time 8 register date
<----------my code
Imports System.Data.SqlClient
Public Class cus_reg Inherits System.Web.UI.Page Dim DS As DataSet Dim iRec As Integer 'Current Record Dim m_Error As String = ""
Public Property MyError() As String Get Return m_Error End Get Set(ByVal Value As String) m_Error = Value If Trim(Value) = "" Then
Label3.Visible = False Else
Label3.Text = Value
Label3.Visible = True End If End Set End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not Page.IsPostBack Then Dim C1 As New MISSQL 'DS = C1.GetDataset("select * from customers;select * from open_ac;select * from accounts") DS = C1.GetDataset("select * from customers;select * from open_ac")
Me.MyDataBind() Else DS = Session("data") iRec = ViewState("iRec") End If Me.MyError = "" End Sub
Public Function BindField(ByVal FieldName As String) As String Dim DT As DataTable = DS.Tables(0) Return DT.Rows(iRec)(FieldName) & "" End Function Public Sub MyDataBind() Label1.Text = "Record : " & iRec + 1 & " of " & DS.Tables(0).Rows.Count txtcu_id.DataBind() txtcu_fname.DataBind() txtcu_lname.DataBind() txtcu_add.DataBind() txtcu_occ.DataBind() txtcu_wplace.DataBind() txtcu_nat.DataBind() txtcu_tel.DataBind() txtcu_fax.DataBind() txtcu_email.DataBind() End Sub
Here is update code
Private Sub bUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bUpdate.Click Dim DT As DataTable = DS.Tables(0) Dim DR As DataRow = DT.Rows(iRec) 'Dim DR1 As DataRow = DT1.Rows(iRec)
If DR.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub End If DR("cu_id") = txtcu_id.Text End If
If txtcu_fname.Text.Trim = "" Then Me.MyError = "please enter your name" Exit Sub Else
DR("cu_fname") = txtcu_fname.Text End If
If txtcu_lname.Text.Trim = "" Then Me.MyError = "please enter your last name" Exit Sub Else
DR("cu_lname") = txtcu_lname.Text End If
If txtcu_add.Text.Trim = "" Then Me.MyError = "please enter your address" Exit Sub Else
DR("cu_add") = txtcu_add.Text End If
If txtcu_occ.Text.Trim = "" Then Me.MyError = "please enter your occupation" Exit Sub Else
DR("cu_occ") = txtcu_occ.Text End If
If txtcu_wplace.Text.Trim = "" Then Me.MyError = "please enter your workplace" Exit Sub Else
DR("cu_wplace") = txtcu_wplace.Text End If
If txtcu_nat.Text.Trim = "" Then Me.MyError = "Please enter your nationality" Exit Sub Else
DR("cu_nat") = txtcu_nat.Text End If
If txtcu_tel.Text.Trim = "" Then
DR("cu_tel") = DBNull.Value Else
DR("cu_tel") = txtcu_tel.Text End If
If txtcu_tel.Text.Trim = "" Then
DR("cu_fax") = DBNull.Value Else
DR("cu_fax") = txtcu_fax.Text End If
If txtcu_email.Text.Trim = "" Then
DR("cu_email") = DBNull.Value Else
DR("cu_email") = txtcu_email.Text End If
Dim Strsql As String If DR.RowState = DataRowState.Added Then Strsql = "insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)" Else Strsql = "update customers set cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10 where cu_id =@P1" End If Dim C1 As New MISSQL Dim cmd As SqlCommand = C1.CreateCommand(Strsql) C1.CreateParam(cmd, "ITTTTTTTTT")
DR.AcceptChanges() Else Me.MyError = "Can not register" End If <---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update Dim DT1 As DataTable = DS.Tables(1) Dim DR1 As DataRow = DT1.Rows(iRec) If DR1.RowState = DataRowState.Added Then If txtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub End If DR1("cu_id") = txtcu_id.Text End If If Label2.Text.Trim = "" Then
DR1("op_date") = Label2.Text End If
Dim StrSql1 As String If DR1.RowState = DataRowState.Deleted Then StrSql1 = "insert into open_ac (cu_id,op_date) values (@P13,@P14)" Else StrSql1 = "update open_ac set op_date=@P14 where cu_id=@P13" End If Dim C2 As New MISSQL Dim cmd2 As SqlCommand = C2.CreateCommand(StrSql1) C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@P1").Value = DR1("cu_id")
cmd2.Parameters("@P2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd2) If Y1 > 0 Then
DR1.AcceptChanges() Else Me.MyError = "Can not register" End If End Sub End Class
<------this is class I use for connecting to database and call parameters....
MISSQL class
Imports System.Data.SqlClient Public Class MISSQL Dim PV As String = "Server=web_proj;uid=sa;pwd=sqldb;" Dim m_Database As String = "c1_itc" Public Strcon As String Public Sub New() Strcon = PV & "database=" & m_Database End Sub Public Sub New(ByVal DBName As String) m_Database = DBName Strcon = PV & "database=" & m_Database End Sub Public Property Database() As String Get Return m_Database End Get Set(ByVal Value As String) m_Database = Value Strcon = PV & "database=" & m_Database End Set End Property
Public Function GetDataset(ByVal Strsql As String, _ Optional ByVal DatasetName As String = "Dataset1", _ Optional ByVal TableName As String = "Table") As DataSet Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DS As New DataSet(DatasetName) Try DA.Fill(DS, TableName) Catch x1 As Exception
Err.Raise(60002, , x1.Message) End Try Return DS End Function
Public Function GetDataTable(ByVal Strsql As String, _ Optional ByVal TableName As String = "Table") As DataTable Dim DA As New SqlDataAdapter(Strsql, Strcon) Dim DT As New DataTable(TableName) Try DA.Fill(DT) Catch x1 As Exception
Err.Raise(60002, , x1.Message) End Try Return DT End Function
Public Function CreateCommand(ByVal Strsql As String) As SqlCommand Dim cmd As New SqlCommand(Strsql) Return cmd End Function
Public Function Execute(ByVal Strsql As String) As Integer Dim cmd As New SqlCommand(Strsql) Dim X As Integer = Me.Execute(cmd) Return X End Function
Public Function Execute(ByRef Cmd As SqlCommand) As Integer Dim Cn As New SqlConnection(Strcon) Cmd.Connection = Cn Dim X As Integer Try Cn.Open() X = Cmd.ExecuteNonQuery() Catch X = -1 Finally Cn.Close() End Try Return X End Function
Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal StrType As String) 'T:Text, M:Memo, Y:Currency, D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture Dim i As Integer Dim j As String For i = 1 To Len(StrType) j = UCase(Mid(StrType, i, 1)) Dim P1 As New SqlParameter
P1.ParameterName = "@P" & i Select Case j
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image End Select
Cmd.Parameters.Add(P1) Next End Sub End Class
<-------Thank you in advance<-------and Thank you very much for all help
I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.
For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.
The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same????! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.
Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?
Or is there a command that tells MSSQL to ignore all relationships for the next query?
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2 ( [PART] ASC, [ORG] ASC, [SPLRNAME] ASC, [REPITEM] ASC, [RFQ] ASC,
