Can I Update Two Tables In A Single Query
Jun 14, 2008hi friends
in my sqlserver 2005 i want to update two tables in a single query.
is it possible? or is it possible by creating a view?
thanks in advance.
shweta
ADVERTISEMENT
Update Multiple Tables In Single Query
Apr 3, 2008Hello All,
I want to update multiple tables using single query and fields name are same of tables.
I am trying like:
update tablename1 t1,tablename2 t2 set t1.fieldname1 = t2.fieldname1 = 'value' where condition;
or
update tablename1 t1,tablename2 t2 set t1.fieldname1 = 'value' t2.fieldname1 = value where condition;
Plzzzzzz help me.Thanx in advance.
Thanx & Regards,
Smita.
Update Multiple Tables Using A Single Query
Nov 6, 2007Hi,
I am using SQl server 2005.
want to update rows in 2 tables,which have a relation on Id field.
Some thing like
Update tblA a , tblB b
Set a.UpdatedDt=getdate(),b.Updateddt=getdate()
where a.Id=b.Id and a.Name='xyz'
can anyone out there help me?
Thanks
Renu
SQL 2012 :: Single Query To Update Same Column Name In Different Tables With Where Condition?
Sep 25, 2014I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052 Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
how to achieve this in single query?
Update 2 Tables In Single Statement
Mar 13, 2008Is it possible to update 2 tables in a single t-sql statement?
If Yes, whats the syntax?
Update Table1,Table2 ... is not working
Can We Insert/Update Into Related Tables In A Single Round Trip ?
Sep 25, 2002I would like to update/insert data into a Orderhearder Table along with the related details into the corrosponding OrderDetails Tables. Can this be done using a single stored procedure or do we have to make one call to the UpdateOrderHeader Stored Procedure and loop thru all the details and call the UpdateOrderDetails Stored Procedure. How do we handle the Transactions in such a case ?
Thanks
Anurag Agarwal
Single Update Query
Apr 29, 2008Table Name:-emp
table structure:- Name Gender
A Male
B Male
C FeMale
D FeMale
I want only one update query to update this table like this
table structure:- Name Gender
A FeMale
B FeMale
C Male
D Male
please help me?
Single Query To Update And Append
May 5, 2004I need to write a single query that will append the values from one table into another table but I need to update a single field in the table with a static value. What I'm working with is an Access .adp with an SQL 2000 backend. The database is used to track ticket sales, payments, and contact info for season ticket holders. Prior to upsizing, each year the database would be copied and then choice bits modified for the next year.
In other words, the history was in several databases, i.e. db2001, db2002, db2003 and for the current year dbcurrent. I decided to create a historic tables in the current DB rather than have umpteen DBs on my SQL server.
My problem is that I need to create a query that appends to my table the previous years data and then updates the season field to reflect the season that the data came from.
In short, say I have a table named accounts
with fields
account, customer, addr1, addr2, ..., ticket type
and I want it to end up in
account_hist with fields
account, customer, addr1, addr2, ..., ticket type, SEASON
and make the season equal say 8 which represents the 8th season the team has played.
I can get both queries to work seperately :D , but for end user ease, I want to perform both actions at the same time :confused: .
Can anybody point me in the right direction?
Thanks
Single Update Query Using In Clause In SP
Jan 4, 2008Hello,
I'm collecting id based on user's selection and storing to ArrayList/String[in asp.net]. Now want to pass those ids stored to stroed procedure. I want SP to update all records in single statement using in like update table set field in (value).
I'm storing id with comma (,) separator. Below is my asp.net code:
string sId=string.Empty;
for(int a=0;a<Id.Count;a++)
{
Id += Id;
if(a!=Id.Count-1)
sId +=",";
}
In database id column is int type. I'm passing id collected with comma separator and passing it as a parameter to SP. but it is giving error related converting to int or similar.
Plz help me create Stored Procedure in SQL 2005 based on above scenario.
Thanks
Update Multiple Rows In A Single Query?
Jul 30, 2007I know this isn't right but I'm trying to build a single query in PHP to re write the sortorder column starting at 0 and writing every row in order.
Code:
update categories set (sortorder=0 where catid=32), (sortorder=1 where catid=33),(sortorder=2 where catid=36) where userid=111
PHP Code:
$qt="update categories set ";
for($i=0;$i<$num;$i++){
$a=$i+1;
$qt.="sortorder=$i";
if($a<$num){
$qt.=", ";
}
}
$qt.=" where userid=111";
Using PHP I can amend the loop above to slot in a row I want so I can change the sort order.
unfortunately I'm not sure how to build such a query in mssql, can anyone help?
Get Result From 3 Tables In A SINGLE Query
Jun 17, 2014I am using 3 tables: projects, project_manager and project_employee
projects
- project_id (int, PK)
- project_name
project_manager
- project_id (int, PK)
- manager_id (int, PK)
project_employee
- project_id (int, PK)
- employee_id (int, PK)
Assume that a manager is currently logged in (so we know what is his ID), what I trying to do is write a query that will display a grid-view showing:
project_id, project_name and number of employees in project.
I tried these quires to test:
SELECT project_id, count(emp_id) as NumberOfEmployees into #tempTable FROM project_employee WHERE project_id IN (SELECT project_id FrOM projects) GROUP BY project_id
SELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_id
Problems are:
1 - I used two queries not one
2 - The result is for all projects of all managers, I just want to display projects for a specific manager (for example mag_id = 5)
3 - it created a temporary table - which I dont want.
Is there anyway to get what I want in a single query?
Joining Four Tables In A Single Query
Mar 19, 2015I have four tables:
a, b, c and d
table a is related to table b by a foreign key. table b is related to c and so on.
I used the sql statement below to join the tables:
$result = mysql_query("SELECT a.colum1, a.column2,
b.column, c.column, d.column FROM a
JOIN b ON a.pkey = b.foreign key
JOIN c ON b.pkey = c.fkey
JOIN d ON c.pkey = d.fkey ")
or die(mysql_error());
[Code] .....
I succeeded in printing out the first record where the four tables are joined, but not other instances.my print out is this:
a.column1
a.colum2
b.colum
c.colum
d.colum
But there are about ten instances where the joining conditions are met. How do I print out all the records that have met the condition?
Single Query - Update Same Column In Diff Table
Sep 25, 2014I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
Transact SQL :: Updating Multiple Tables In A Single Query?
Apr 27, 2015Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies View RelatedRegarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.
Mar 8, 2008Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
Groups Gr,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Gr.G_ID_PK =Aud.Audit_Group and
Ad_ID_PK in (select Ad_ID_PK from Audits)
Now i want to edit these names.
when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table.
For example:
i have this data in my audits table:
Commercial83312
2
2
2
1
Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs.
when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table.
so the data may be changed in audits table as :
Commercial83312
4
2
2
1
so here the industry ID is changed
I need the stored procedure for this.
please help me,its very urgent...
How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?
Mar 18, 2008This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
T-sql To Update 2 Tables With 1 Query?
Nov 30, 2005is 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 RelatedHelp With Update Query Linking 3 Tables
Jul 23, 2005I am looking for some assistance with an update query that needs to link 3tables:This query ran and reported over 230,000 records affected but did not changethe field I wanted changed, not sure what it did.I did notice that the "name" in "GM_NAMES.name" was colored blue in QueryAnalyzer. Is it bad to name a column "name"?UPDATE ABSENCESset CustomerContactID = cicntp.cnt_idfrom absences, cicntpwhere (SELECT cicntp.cnt_l_nameFROM cicntp INNER JOINgm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOINAbsences ON cicntp.cnt_id = Absences.CustomerContactID)Next I tried this query which is still running after 75 minutes (on alaptop)update absencesset CustomerContactID = cicntp.cnt_idfrom absences, cicntp, gm_nameswhere gm_names.name= cicntp.cnt_l_nameAs you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.Absences.CustomerContactID is what I need updated, when finished it shouldmatch CICNTP.cnt_idGM_NAMES is a temp table and matches records in CICNTP.cnt_l_nameCan some of you school this newbie on the best way to do this?Thanks a bunch!
View 4 Replies View RelatedUpdate Query When Joined Tables Are Involved
Sep 30, 2013I have a query written that filters on joined table data. The SELECT looks like this:
SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC
If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:
UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages
Nov 11, 2007I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
Update Query Joining Tables From Separate Databases
Apr 17, 2008
In database DB1, I have table DB1.dbo.Suppliers1. This table has an ID column of type INT named SUPPL1_ID
In database DB2, I have table DB2.dbo.Suppliers2. This table has an ID column of type INT named SUPPL2_ID
I would like to update DB2.dbo.Suppliers2 based on values from DB1.dbo.Suppliers1 joining on SUPPL1_ID = SUPPL2_ID.
How can I do this in SSIS?
Assumptions:
linked servers are not an option, as I want the SSIS package to be portable and not dependent on server environments.
TIA.
-El Salsero
Urgent: Help Regarding Query To Update Dates In Related Tables
May 28, 2007I am really stuck here,
I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db,
1- Stages
2- Activities
3- Tasks
4- Subtasks
Structure is like:
Stages
1- SrNo (Unique)
2- Stage
3- StartDate
4- FinishDate
Activities
1- SrNo (Unique)
2- Activity
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Stages Table)
Tasks
1- SrNo (Unique)
2- Task
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Activities Table)
Subtasks
1- SrNo (Unique)
2- Subtasks
3- StartDate
4- FinishDate
5- SrNo1 ((FK) from Task Table)
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages.
I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
Any help, would be appreciated.
Thanks
Plz Help...update Value In Multiple Db Table Using Single 'update Command'
Mar 18, 2005hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
SQL Server 2012 :: How To Perform Update Query That Involves Multiple Tables
Aug 31, 2015I am trying to run an update statement against a vendor's database that houses HR information. If I run a regular select statement against the database with the following query, it returns without error:
SELECT "QUDDAT_DATA"."QUDDAT-INT", "NAME"."INTERNET-ADDRESS", "QUDDAT_DATA"."QUDFLD-FIELD-ID", "QUDDAT_DATA"."QUDTBL-TABLE-ID"
FROM "SKYWARD"."PUB"."NAME" "NAME", "SKYWARD"."PUB"."QUDDAT-DATA" "QUDDAT_DATA"
WHERE ("NAME"."NAME-ID"="QUDDAT_DATA"."QUDDAT-SRC-ID") AND "QUDDAT_DATA"."QUDTBL-TABLE-ID"=0 AND "QUDDAT_DATA"."QUDFLD-FIELD-ID"=16 AND "QUDDAT_DATA"."QUDDAT-INT"=11237When I try to convert it into an
[Code] ....
I am assuming I am receiving this error because it doesn't know where to find QUDDAT-INT? How can I fix that?
The "QUDDAT-INT" column houses the employee number. So in the case of the SELECT query above, I am testing against a specific employee number.
Update SubQuery(two Tables), Pls Advice Wats Wrong With The Query. 1 Hole Day I Spent Still Cant Figure
Oct 27, 2006Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
How To Update Single Column
Oct 25, 2007hi guys i have a problem;;;
i have to update table'''
querey is--TABLE1 HAS A COLUMN CALLED CITY .THE RECORDS WITH CITY='DELHI' ARE IS TO BE UPDATED WITH CITY='MUMBAI' AND THE RECORDS WITH CITY='MUMBAI' ARE IS TO BE UPDATED WITH CITY='DELHI' ...
i HAVE TO WRITE A SINGLE SQLSTATEMENT TO DO THIS NOT WITH THE HELP OF CURSOR
Single SP For Insert And Update
Apr 4, 2014I Want to write a single SP which inserts and updates.
CREATE PROCEDURE sp_NewPassword
@UserName nvarchar(50),
@OldPassword nvarchar(50),
@NewPassword nvarchar(50)
AS
BEGIN
INSERT INTO RGH_ChangePassword(vUserName, vNewPassword) VALUES(@UserName, @NewPassword)
UPDATE RGH_LoginMaster SET vPassword=@NewPassword WHERE vUserName=@vUserName and vPassword=@OldPassword
END
When i tried to create above sp it gives me an error saying
Msg 137, Level 15, State 2, Procedure sp_NewPassword, Line 8
Must declare the scalar variable "@vUserName".
Single SQL Update Statements
Oct 31, 2007
What is the single SQL statement to truncate the blank space on either side of data.
Ex.
Table1 has Name as column.
I have records filled with blank space on both side for Name field.
With one query I want to correct (truncate the leading and trailing space) the data.
How?
SQL Server 2005 SP2.
Thank you,
Smith
Multiple SET/WHERE Within Single UPDATE?
Mar 21, 2008
Can an Update statement support multiple SET/WHERE clauses? The following example won't work, but this is what I'd like to be able to do:
Code Snippet
UPDATE [CPSAgacar].tmp_CTA2
SET [Admit Date] = NULL
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL
SET CalcDischDate = Discharge
WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL AND CPSAgacar].tmp_CTA2.CalcDischDate IS NULL
Thanks.
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Mar 21, 2008Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
Single Statement For Insert Or Update
May 8, 2008 In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else
End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing
Is there an ADO.Net equivalent?
thanks,
Trigger To Update One Record On Update Of All The Tables Of Database
Jan 3, 2005hi!
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
Update Multiple Records From A Single ASP.NET Webpage
Mar 3, 2004Got a beginner question here...
Let's say I have a database table that houses server information with four columns: make, model, serial #, ip address. And assume there are ten rows with that information filled out. How could I display all the rows of information on a single webpage (ASP.NET), with all the fields being editable; and a single save button that would send any changes to the database (in reality I guess it would be sending all rows and fields to the database, and just overwrite the previous data).
Could a page such as that be created using FrontPage 2003 or Dreamweaver MX 2004?
This would be strictly for updating information. I would have a separate form for adding a new entry.
Thanks for your help.