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
Hi,
I have an error for the following statement:-
DECLARE @MONTHNO char
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
DECLARE @batchno int
DECLARE @ACTIVEYEAR int
set @batchno = 5
set @ACTIVEYEAR = 2007
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
exec('update bmaster set '
+@DB+'='+@DB+' mysursor.damt,'
+@CR+'='+@CR+' mysursor.camt from bmaster inner join (select transact.year,transact.acctno,'
+' sum(transact.camt) as camt,'
+' sum(transact.damt) as damt,'
+' sum(transact.ccamt) as ccamt,'
+' sum(transact.cdamt) as cdamt'
+' from transact where transact.batchno='+@batchno+ ' and transact.year='+@ACTIVEYEAR
+' group by transact.year,transact.acctno) as mysursor on bmaster.year=mysursor.year and bmaster.acctno=mysursor.acctno')
the error as follows:-
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'mysursor'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
thanks for your help.
OK I have my SSIS Dataflow behaving the way it should but how does one map to a table that needs an updatde to a table.
In this case it is an Oracle Table so the SQl would liik like this...
UPDATE PPS_PRINCIPALSSET NAME = @Variable2WHERE (PRINCIPAL_ID = @Variable1)
I cannot figure out how to update ufd column from this statment. I can select the columns I want to update but how can I change it to update them from the select?
SELECT ufd.* FROM Tbl uf
JOIN Received rr
ON uf.ID = rr.SSN AND uf.Id = 36
JOIN Tbl ufd
ON uf.S_Id = ufd.S_Id
WHERE uf.Col <> 'Employee' AND ufd.Id = 35
I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.
this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
It is using ole db connection. About 36,000 records that it is updating.
I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?
any input greatly appreciated.
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
Hi
I'm trying to compare two varchars to check if they are the same, if they are the same then the color must turn red, if not then they must remain black
SELECT *
from members m, client c
where C.ClientID = m.ClientID
AND c.ClientID in (87,86)
AND m.email in ('dassd@fdskjh.com','asdfas@sdfd.net', etc...)
my results will give me two of the same email addresses but with different ClientID's, now when it
finds the same email it needs to make them both "RED"
Please help, any advice would be helpful
Kind Regards
Carel Greaves
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
update table1 set field1=null where id=1; commit;
where I can look for the user,who execute this statment?
It was 6.02.2007 or 7.02.2007.
All users connect using SQL Serwer Authentication.
It is test database and from 1 to 3 users connect to it, and submit not more then 10 SQl statmant per day.
Hi all
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
Thank you in advance .....
Regards,
sms
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 RelatedThis 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))
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.
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 View RelatedI 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
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")
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
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
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.
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?
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 Relatedis 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 RelatedI 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
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
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))
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
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 & "' )"
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)
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;
MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)