I have 2 database tables with column "city". When I update one of these two I want to be updated all the records in the other table. For example:
In table "a", column "city" contains the record "NY"; in table "b", column "city" also contains the record "NY" several times. When I change "NY" to "New York" in table "a" I want that also in table to be changed all the records. The condition is not to use foreign key, but just the text of the records.
Any help would be appreciated,thanks.
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?
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
This is my curren code for updaing ESN number. But this is incorrect. <asp:SqlDataSource ID="ESNTrackingDataSource" UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId" OnInit="ESNTrackingDataSource_Init" OnUpdating="ESNTrackingDataSource_Updating" OnUpdated="ESNTrackingDataSource_Updated" runat="server"> <UpdateParameters> <asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter> </UpdateParameters></asp:SqlDataSource> this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId. This is my query that returns the ones are not assigned to and Asset SELECT DISTINCT EsnId, EsnNumberFROM dbo.ESNTrackingWHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have an package that goes out and downloads information from an Informix Database. This Information needs to be replicated in another table. However I cant just delete and insert the information into the table, There are a number of clients updating from this table once a minute. How can I make SQL Serve 2000 update the information based on another sql table ( Sort of like a local replication?). Is there a simply query I can use or do I need to set up another packeage stored procedure. Right now the information is placed in a tempory table, the actual table needing to be updated is cleared of its data, and the information is moved. However this causes latency issues in my program. Also, since the Infromix query can take up to 30 seconds to complete, this leaves some of my users retrieving blank data.
I want to update 2 tables in one sp as the parameters are the same. Here's my sp and I don't get any syntax errors in Ent manager, but the error 'Incorrect syntax near 'spRB_CancelFacMenuBooking' when I run it.
AS BEGIN UPDATE tblRB_FacilitiesBookings SET FB_Cancelled =@strCancelled, FB_CancelledDate=@strCancelledDate, FB_CancelledBy=@strCancelledBy WHERE FB_BookingNo = @strBookingNo END
BEGIN UPDATE tblRB_MenuBookings SET MB_Cancelled =@strCancelled, MB_CancelledDate=@strCancelledDate, MB_CancelledBy=@strCancelledBy WHERE MB_BookingNo = @strBookingNo END GO
hi i have two table and would like to update a one of them from the other. i have to fill the column "num" of the CLIENT table , from the TAB1 table. with the condition CLIENT.id = TAB1.id UPDATE [client] INNER JOIN nom_tab1 ON [client].id = tab1.id SET [client].num = [tab].[num]; but it didn't worked for me ,can u give me a hand ?
Hi, I have a problem with adding new rows to a table in access ADP file. Most of my tables i have created using SQL Server Management Studio and i cant edit those with ADP When i create table using ADP project file I can easly edit add delete rows the way i want.The problem is that most of them are already created using SQL Server Management Studio so i cant change them in ADP. i was thinking that the properties are different when its created by adp project but they loook the same. Dont anybody knows what to do to be able to edit tables in adp.
<----------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
Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help. Thanks
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details. Is it possible for me to update both pages in 1 web update page?????
hi,does any know how to update two tables with the same statement?is it possible?here is my unsuccessful try:UPDATE A,BSET A.VAL='BLUH1', B.VAL='BLUH2'from #T A, #T2 BWHERE A.LOANID=B.LOANID
is it possible to do something likeUPDATE Table1, Table2 INNER JOIN Table2 ON Table1.ID=Table2.ParentID SET Table1.Name=xxx, Table2.Address=yyyI want to update at same time the Asp.net Users table and a "UsersDetails" table, sharing the same UserID key.
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.
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and Asset
SELECT DISTINCT EsnId, EsnNumber FROM dbo.ESNTracking WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I have recently defragged my SQL server using INDEXDEFRAG. Can somebody please tell me how to update the statistics on all the tables? Thanks in advance.
Below is the script that I executed to defrag all the tables in my database if anyone needs this.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow SELECT @maxfrag = 20.0
-- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END
-- Close and deallocate the cursor CLOSE tables DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor OPEN indexes
-- loop through the indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
I have 2 tables. They both have the same fields [OFO_Code] and [Description]
Table 1 is Called Qualification and Table 2 OFO_Code. The second table is a lookup table. The info in Table 1 's description column in some places does not match that of Table 2.
I am trying to loop through these tables to Update the info in Table 1's description to match the correct description in Table 2.
Their are 1417 items in Table 2 and 77000 items in table 1.
There are NO errors when executing, just no update
Here is my code so far:
Code: Update Qualification SET [Description] = B.Description FROM Qualification A INNER JOIN OFO_code B ON A.OFO_CODE = B.OFO_CODE WHERE A.OFO_Code IN ('" & OFO & "' )"
how to update a temp table with another temp table for example I have #tempdashboard that has info in it.I also have #tempappscount of which I want to insert the info inside it into #tempdashboard...I believe the column in #tempdashboad is DailyAppsInINT,
I did a query that gave me the number of daily apps created. I was told to create #tempappscount and my query to dump into #tempappsaccount. Then dump the info from #tempappsaccount into #tempdashboard.
I have a customers CRM DB that I need to run an update query on, affecting around 14,000 records. The fields used in the entity in question are split across two tables. I need to update a field in one tabled based off of the result of a field in the other table.
So this is what I started with:
USE db1 UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';
This didn't work, SQL complained about being unable to bind tbl1.CreatedOn. I assume because it's in a different table to the one I'm updating.
I attempted a JOIN to the best of my limited SQL knowledge, thinking I could just shove the two tables together and it might be happy.
USE db1 INNER JOIN tbl1 ON tbl2.Id=tbl1.Id; UPDATE tbl2 SET field1 = '1' WHERE tbl1.CreatedOn < '2013-28-09 00:00:00.000';
This also didn't work, complaining of syntax error near 'INNER'
I'm obviously missing something, but IU don't know what it is.
Given the below table relationships, I am trying to update the price of disk by %20 percent that were interpreted by 'Joe Smith'. I took a crack at this, but I do not feel comfortable with it. Do I need to do some type of join or union?
UPDATE Disk SET price = price *.20 WHERE Interpreter.name = ‘Joe Smith;
ans writes "I’m using SQL Server • I’ve got 3 tables: tblOne, tblTwo, tblThree • Using the stored procedure(let call this stored procedure sp_Result) I generate result using all the above tables(tblOne, tblTwo, tblThree). • The problem is, it takes too long to execute(around 15 sec), which is not acceptable as I’m using in the result in the web application • I’m thinking of scheduling to run the above stored procedure to run once every hour and write the result in a new table, tblResult. • now the user only queries in the tblResult tables. Since it is a straight forward query it will take less than 1 sec. Perfect !!! • Now the tblResult already exist, and the user are queries only to this table. • How would I compare the result produced by stored procedure sp_result and the table tblResult? • How would I update tblResult with the produced result? • Would be a good idea to write the result in a new table called temp, then drop the table tblResult and then rename the table temp to tblResult?"
Hi, I am using sqlServer 2000.I want to insert as well as update Two Tables at the Same Time using storedprocedure.Both My Tables Have the Same columns only difference being the name.My Tables are, ADDRESS AND ADDRESSBOOK and their values are Name, Address, Place, Date,City. How can achieve this task using stored Procedures.?
Visual Studio 2005 i have insert and update statements. they don't produce any errors but no changes happen in my tables. are there any possible reason for this. i used sqlcommand and even stored procedures but still the same.
Table1(T1) contains Column1 (C1) which must be appended to Column2 (C2) in Table2 (T2) where an identifying code column(ID) matches in each table. An example of what I want is, where T1.C1 = 'Aluminum' and T2.C2 = 'Material', I would like it to be updated to T2.C2 = 'Material: Aluminum'. I tried this:
UPDATE T2 SET T2.C2 = (SELECT T1.C1 FROM T1 WHERE T1.ID = T2.ID) + ':' + T2.C2
HiMy database contains , lets say, 100 tables. Out of these 100 tables,60 tables contain both columns; Column1 and Column2.I want to:Update (All tables with BOTH of these columns)Set Column1 = Column2Is there a better way of doing it? OR do I have to do it manually foreach table.Thanks for your help.
Hello and thank you for your time. I have never worked with sqlserver and currently have a project updating the database.I have a CSV file as follows:idkeywords (this field contains commas within)dateI have a sql server table as follows with only the id populated.idkeywordsdateHow do I update the sql server fields from the CSV file? I'm assumingI would use an UPDATE statement. What SQL server utility would I use(I'm very new at this, please be basic)?Thank you once again,Sunshine
Hi,I tried to use the following query to update a value in one table witha value from another table:UPDATE tbl1SET col1 = tbl2.col2FROM tbl1, tbl2WHERE tbl1.[id] = tbl2.[id]but it won't work. I also tried this with a subquery using "TOP 1",but that wouldn't work either. SQL Server 2000 gives me the followingerror-message:"Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated."What is wrong? It seems that the join is not functioning properly, butit functions properly with a rather 'simple' SELECT. How to updatethose fields, other than by hand ;)Regards,Falco Vermeer
I have a Gridview (edit enabled), connected to a SQLDataSource. SQLDataSource populates the gridview from 1 SelectCommand. figure1: ColumnA | ColumnB | ColumnC
I have 2 SQL Server 2005 tables: Table1 and Table2 Now on my SQLDataSource's UpdateCommand, I want the value of ColumnB to go to Table1's ColumnB, and ColumnC to go to Table2's ColumnC. How do I do this? I understand I can't do this with 1 UPDATE-SET sql query. Maybe I can with stored procs or something, but im kinda noobish when it comes to this. How? Thanks
