Update SQL Tables
Jan 16, 2004
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.
View 2 Replies
ADVERTISEMENT
Jan 3, 2005
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
View 10 Replies
View Related
Dec 7, 2006
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 Related
Jul 25, 2006
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))
View 1 Replies
View Related
Jul 18, 2007
Hi,
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.
View 16 Replies
View Related
Oct 5, 2005
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.
CREATE PROCEDURE spRB_CancelFacMenuBooking
@strBookingNo integer,
@strCancelled bit,
@strCancelledDate datetime,
@strCancelledBy nvarchar(100)
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
View 9 Replies
View Related
Sep 2, 2007
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 ?
View 12 Replies
View Related
Aug 9, 2006
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.
View 1 Replies
View Related
May 14, 2007
<----------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")
Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()
Dim Dtr As
DataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =
DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
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")
cmd.Parameters("@P1").Value = DR("cu_id")
cmd.Parameters("@P2").Value
= DR("cu_fname")
cmd.Parameters("@P3").Value = DR("cu_lname")
cmd.Parameters("@P4").Value = DR("cu_add")
cmd.Parameters("@P5").Value = DR("cu_occ")
cmd.Parameters("@P6").Value = DR("cu_wplace")
cmd.Parameters("@P7").Value
= DR("cu_nat")
cmd.Parameters("@P8").Value = DR("cu_tel")
cmd.Parameters("@P9").Value = DR("cu_fax")
cmd.Parameters("@P10").Value = DR("cu_email")
Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
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
View 2 Replies
View Related
Mar 10, 2008
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
View 1 Replies
View Related
May 12, 2008
Hi,
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?????
Thanks in advance.
View 3 Replies
View Related
Jun 15, 2004
i want to update a table where a linked table is a certian value
for example
i have a table that queues up calls and has whether they have been contacted or not as a boolean value.
but if the call is closed in another way i would like to update that table where the linked location table has a certian value
but in the query analizer it will only let you use one table to update is there another way to do this?
View 2 Replies
View Related
Sep 16, 2005
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
View 3 Replies
View Related
Nov 30, 2005
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.
View 4 Replies
View Related
Sep 11, 2000
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
View 1 Replies
View Related
Nov 1, 2004
Hi
After running some queries, I want to know which tables have been updated in the database in sql server.
Is there a way to find out the last updated time of all the tables in the database?
Thanks
Madhukar Gole
View 1 Replies
View Related
Sep 26, 2007
Hi guys,
Does anyone who how I update two tables with one UPDATE statment (i.e. joining them with an INNER JOIN). I'm using SQL Server.
These are going to be very large tables, so I'd rather not have to run an UPDATE twice as it's going to be s l o w...
Thank all
Dâi
View 1 Replies
View Related
Jul 25, 2006
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, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
View 1 Replies
View Related
Dec 8, 2006
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'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- 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
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
View 4 Replies
View Related
Feb 27, 2014
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 & "' )"
View 7 Replies
View Related
May 1, 2008
this code is not working.
I can’t update 2 tables in one statement?
update pd, p
set pd.show = 0 , pd.seashow = 0
FROM ProductDetail pd
join Products p on p.itemid = pd.ItemID
where pd.show = 1
and pd.site in (1,4,6)
View 3 Replies
View Related
Aug 19, 2013
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.
View 2 Replies
View Related
Oct 15, 2014
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.
View 5 Replies
View Related
May 7, 2006
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;
MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)
View 3 Replies
View Related
Aug 7, 2006
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?"
View 6 Replies
View Related
Oct 11, 2006
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.?
Any Help would be appreciated....
Thanks...
View 8 Replies
View Related
Mar 28, 2007
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.
View 18 Replies
View Related
Mar 28, 2008
I have a mess:
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
This did not work out. How should I fix this?
View 6 Replies
View Related
Jul 20, 2005
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.
View 5 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Mar 16, 2007
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
View 1 Replies
View Related
Jun 5, 2007
hi all,i'm working on a formview which will update 2 tables which are linked together but..i'm getting an error of the control not being found.my code is as shown below <asp:SqlDataSource ID="invDetSrc" runat="server" ConnectionString="<%$ ConnectionStrings:rockwellConnectionString %>" ProviderName="<%$ ConnectionStrings:rockwellConnectionString.ProviderName %>" SelectCommand="SELECT rockwell.logix_asset_list.Inventory_ID, rockwell.vendor.Vendor_Name, rockwell.logix_asset_list_category.Category_Name, rockwell.logix_asset_list.Total_Quantity, rockwell.logix_asset_list.Quantity_In_Stock, rockwell.logix_asset_list.Part_Number, rockwell.logix_asset_list.On_Order, rockwell.logix_asset_list.ImageUrl, rockwell.logix_asset_list.DrawingUrl, rockwell.logix_asset_list.Description FROM rockwell.logix_asset_list, rockwell.logix_asset_list_category, rockwell.vendor WHERE rockwell.logix_asset_list.Logix_Asset_List_Category_ID = rockwell.logix_asset_list_category.Logix_Asset_List_Category_ID AND rockwell.logix_asset_list.Vendor_ID = rockwell.vendor.Vendor_ID AND rockwell.logix_asset_list.Part_Number = ?" UpdateCommand="UPDATE vendor, logix_asset_list set vendor.vendor_name= @Vendor_Name, logix_asset_list.Total_Quantity=@Total_Quantity, logix_asset_list.Quantity_In_Stock=@Quantity_In_Stock, logix_asset_list.Part_Number=@Part_Number, logix_asset_list.On_Order=@On_Order, logix_asset_list.Description=@Description WHERE logix_asset_list.Vendor_ID = vendor.Vendor_ID and logix_asset_list.Inventory_ID = @Inventory_ID" > <SelectParameters> <asp:ControlParameter ControlID="rstGrid" Name="?" PropertyName="SelectedValue" /> </SelectParameters> <UpdateParameters> <asp:ControlParameter Type="Int32" ControlID="Total_Quantity" ConvertEmptyStringToNull="true" Name="@Total_Quantity"/> <asp:ControlParameter Type="string" ControlID="Vendor_Name" ConvertEmptyStringToNull="true" Name="@Vendor_Name"/> <asp:ControlParameter Type="Int32" ControlID="Quantity_In_Stock" ConvertEmptyStringToNull="true" Name="@Quantity_In_Stock"/> <asp:ControlParameter Type="string" ControlID="Part_Number" ConvertEmptyStringToNull="true" Name="@Part_Number"/> <asp:ControlParameter Type="string" ControlID="On_Order" ConvertEmptyStringToNull="true" Name="@On_Order"/> <asp:ControlParameter Type="string" ControlID="Description" ConvertEmptyStringToNull="true" Name="@Description"/> <asp:ControlParameter Type="Int32" ControlID="Inventory_ID" ConvertEmptyStringToNull="true" Name="@Inventory_ID"/> </UpdateParameters> </asp:SqlDataSource> <asp:FormView ID="FormView1" runat="server" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataSourceID="invDetSrc" ForeColor="Black" GridLines="Vertical" DataKeyNames="Inventory_ID"> <EditItemTemplate> <table border="0" cellpadding="3" cellspacing="3" style="background-color: white; color: black;"> <tr> <td colspan="2"> <asp:Image ID="Image1" runat="server" Height="150px" ImageUrl='<%# "~/Image/photo/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("ImageUrl") %>' Width="150px" /> <br /> <asp:HyperLink ID="drawingLink" runat="server" NavigateUrl='<%# "~/Image/drawing/Logix%20Asset/"+Eval("Category_Name")+"/"+Eval("DrawingUrl") %>' Target="_blank" Text="View Drawing" ForeColor="Blue"></asp:HyperLink> </td> <asp:HiddenField ID="HiddenField1" runat="server" Value='<%# Bind("Inventory_ID") %>' /> </tr> <tr> <td style="color: black"> Part Number:</td> <td> <asp:TextBox ID="Part_Number" runat="server" Text='<%# Bind("Part_Number")%>'></asp:TextBox> </td> </tr> <tr> <td> <strong style="color: black">Vendor:</strong></td> <td> <asp:TextBox ID="Vendor_Name" runat="server" Text='<%# Bind("Vendor_Name")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Total Quantity:</strong></td> <td> <asp:TextBox ID="Total_Quantity" runat="server" Text='<%# Bind("Total_Quantity")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Quantity In Stock:</strong></td> <td> <asp:TextBox ID="Quantity_In_Stock" runat="server" Text='<%# Bind("Quantity_In_Stock")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black"> <strong>Quantity on Order:</strong></td> <td> <asp:TextBox ID="On_Order" runat="server" Text='<%# Bind("On_Order")%>'></asp:TextBox> </td> </tr> <tr> <td style="color: black; height: 44px;"> <strong>Description:</strong></td> <td style="height: 44px"> <asp:TextBox ID="Description" runat="server" Text='<%# Bind("Description")%>' Height="53px" TextMode="MultiLine" Width="150px"></asp:TextBox></td> </tr> <tr> <td> <asp:Button ID="EditBtn" runat="server" Text="Submit Changes" Width="106px" CommandName="Update" /></td> <td> <asp:Button ID="Cancel" runat="server" Text="Cancel" CommandName="cancel" /></td> </tr> </table> </EditItemTemplate> </asp:FormView>any help would be much appreciated!
View 1 Replies
View Related
