How To JOIN In UPDATE - Across 2 Tables
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
ADVERTISEMENT
Feb 21, 2012
I have two tables A and b
A
projectID Setid value
301 1 abc
301 2 xyz
302 4 def
..... .... ..
B
SettingsId Setvalue
1 ter
2 yet
4 44
... ....
I want to update all the Setid of table A with that of values from table b. How can i do this?
View 7 Replies
View Related
Jun 13, 2008
Hi all,can somebody help to write this stored procedure Table1 Table2LogID MigIDUserMove LogIDUserNew Domain User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks
View 1 Replies
View Related
Aug 17, 2007
Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
View 3 Replies
View Related
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
Jun 30, 2004
Hi all,
Can someone please help me with this update statement?
UPDATE Stats
SET Stats.JobShownInResults = Stats.JobShownInResults + 1
WHERE Jobs.JobID IN
(
SELECT Query that returns IDs from Search criteria
)
From Stats
inner join Jobs
ON Jobs.JobId = Stats.JobID
I'm trying to increment a value in 'Stats' every time a job in 'Jobs' is returned in a search.
Any help much appreciated,
pete
View 5 Replies
View Related
Mar 22, 2013
In the last weeks I came to work with SQL Server more closely and - not being used to it - I stumbled over the sematics of an UPDATE statement using a JOIN (something which is not available in e.g. Oracle).I wonder what the difference between these two updates is:
Code:
update foo
set ..
from bar
where bar.fid = foo.id;
and
Code:
update foo
set ...
from foo f1
join bar on bar.fid = f1.id;
In both cases I have an inner join between foo and bar, but in the second one, foo is actually listed twice in the update statement. As far as I can tell, both carry out the same thing - at least with my test data.
View 10 Replies
View Related
Apr 23, 2008
Best Greetings,
i want to make an update query for a table but the where clause will check a join with other talbe
update table1 inner join table 2 on x.table1=y.table2 and y.table1= y.table2
set z='anything'
or shall it be
update table1
set z='anything'
where x in (select x from table2) and y in (select y from table2)
i dont know how the syntax be any idea plz ,also table 2 it will be derived from table1 in the form of
select x,max(y)
from table1
order by x
View 2 Replies
View Related
Jan 29, 2015
UPDATE sku
set ecomm = 1
from sku
join invt
on sku.style = invt.style
where invt.first_rcvd = '12/22/2014'
I keep getting an error using this query. It keeps failing at the "FROM" portion. What I want to do is update column ECOMM within table SKU if the STYLE has been received on a certain date.
View 4 Replies
View Related
Jun 16, 2006
in a table TBL1 I have to set DESCRIPTION for a TYPE1 equal to DESCRIPTION for a TYPE2 where their ID is equal in TBL1 and their key fields appear together in another table TBL2. In english, in TBL1 the description and id are equal but the type is different. a relationship between their key fields is shown in TBL2. Any thoughts on how to write this?
View 6 Replies
View Related
Jul 6, 2006
I am trying to update a file based on data retrieved from a join and performing a calculation prior to updationg my result field. I end up with "Column qualifier or table B undefined. "
can anyone see what my problem is?
update a
set a.yr2004 = (b.smal + b.smat) * a.qtypre
from commodityf a inner join itmrvb b
on a.i@stid = b.stid
and a.cinbr = b.itnbr
View 7 Replies
View Related
Aug 29, 2007
Here is my update statements which doesn't work, can you show me an example.
UPDATE zurnacik_user SET zurnacik_user.usergroupid=15
INNER JOIN zurnacik_userfield
ON zurnacik_user.userid = zurnacik_userfield.userid
WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2
=================
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE zurnacik_userfield.field5 = "Kadýn"
AND zurnacik_user.usergroupid = 2
SE' at line 4
=====
I from Turkey, Thank You...
View 2 Replies
View Related
Jul 23, 2005
Hello,I have two tables (table1 and table2). I want to set a flag in table1 foreach common row with table2. I use the following syntaxUPDATE table1 SET flag='Y' from table1 INNER JOIN table2 on (table1.a =table2.a) AND (table1.b = table2.b)However the situation arises where I may have a row in table2 that matcheswith two or more rows in table1. The requirement is that I only want toset the flag on a single row in table1.
View 3 Replies
View Related
Nov 28, 2006
I am trying to update 1 table with the top records from another table foreach record in the first tableUPDATE HPFSLOWMOVINGSET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOINHPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR ANDHPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE WHERE DOCTYPE = 4)This updates all records with the same lastrctdate. I need to update eachrecords with the top lastrctdate where the itemnmbr and locncode equals.Thanks for any help you can provide!Darren
View 4 Replies
View Related
Nov 15, 2006
I have an MS Access query that needs to be converted ot SQL Server 2005
Access Query:
UPDATE tblCheckNumber INNER JOIN tblHistory ON
tblCheckNumber.Autonumber = tblHistory.AutoNumber SET
tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber =
[tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate],
tblHistory.AccountNumber = [tblchecknumber].[AccountNumber],
tblCheckNumber.Updated = "YES"
WHERE
(((tblHistory.CheckAmount) Is Null Or
(tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND
((tblHistory.CheckDate) Is Null) AND
((tblHistory.AccountNumber) Is Null));
SQL conversion:
UPDATE
tblCheckNumber
SET tblHistory.CheckAmount = ([tblchecknumber].[amount1]), tblHistory.CheckNumber = [tblchecknumber].[checknumber], tblHistory.CheckDate = [tblchecknumber].[checkdate], tblHistory.AccountNumber = [tblchecknumber].[AccountNumber], tblCheckNumber.Updated = "YES"
FROM [DEV_TAXREF].[dbo].tblCheckNumber
INNER JOIN tblHistory
ON tblCheckNumber.Autonumber = tblHistory.AutoNumber
WHERE (((tblHistory.CheckAmount) Is Null Or (tblHistory.CheckAmount)=0) AND ((tblHistory.CheckNumber) Is Null) AND ((tblHistory.CheckDate) Is Null) AND ((tblHistory.AccountNumber) Is Null));
I get the following error:
The multi-part identifier "tblHistory.CheckAmount" could not be bound.
What is wrong?
View 3 Replies
View Related
May 30, 2007
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server.
JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
View 2 Replies
View Related
Jan 28, 2008
Hi all,I have a problem with a sql update statement in a store procedure :update table1 set id_cl=t2.id_cl, mail=t2.mail from [table1]t1 right join [table2]t2 on t1.id_cl=t2.id_clWhat I would like is :- Update mail in table 1 from table2 when id_cl are identical -> works- Insert a new id_cl in table1 if it exists in table2 and not in table1 -> doesn't workI thought that the 'right join' would have been able to do that but apparently not.Could you help on this ?Thank you
View 4 Replies
View Related
Mar 21, 2004
I have a page that will require several hundred update queries to be sent to the database. How much of a performance increase will i get by joining them all into one statement and sending them as a batch instead of running them one by one?
Thanks.
View 5 Replies
View Related
Jun 2, 2004
Ok, I have tried this a million ways, and I am just stumped...
I have a access statement I am trying to convert to a T-SQL Statement.. There is no reason I should be having such a hard time with a simple query. Please Help!
UPDATE Tbl1
INNER JOIN Tbl2 ON Tbl1.ID = Tbl2.ID
SET tbl1.Field1 = tbl2.Field1
View 2 Replies
View Related
Nov 21, 2005
I am tryin t to update the tbl1_ID from the tbl2_ID. How do I do a Update Join that will do a comparison on the param column and value columns so that I could get the correct ID into tble 1.
Tbl1 is my destination table and tbl2 is my source. Please Help.
ID Tbl1_ID tb1Param tbl1value
1NULLParam1 0
1NULLParam2 F
1NULLParam3 2
3NULLParam1 0
3NULLParam2 E
3NULLParam3 0
5NULLParam1 0
5NULLParam2 F
5NULLParma3 2
tbl2_ID tbl2Param tbl2value
100param1 0
101param1 1
102param1 2
103param1 3
104param1 4
105param2 E
106param2 F
107param2 H
108param2 HF
109param2 HS
110param2 L
111param2 LS
112param3 0
113param3 1
114param3 2
115param3 3
116param3 4
117param3 5
118param3 6
Here is what Im trying to do if you can understand this.
Update Tbl1
SET tb1ID = B.tbl2_ID
FROM tbl1 AS A JOIN tbl2 AS B
ON A.tbl1Param + A.tbl1Value = B.tbl2Param + A.tbl2value
View 2 Replies
View Related
Aug 24, 2004
I want the sum of an amount to update another table using inner join.
The following query -
UPDATE A
SET A.[Col3] = SUM(B.[Col3])
FROM A
INNER JOIN B
ON A.C1 = B.C1
ANDA.C2= B.C2
Gives me an error saying I cannot use SET with aggregate Column.
How do I perform this aggregate update?
Thanks,
Vivek
View 1 Replies
View Related
Apr 7, 2008
I have three table For example
Employee (Empid , Empname , Esal)
Department (Deptid , Deptname , empid )
Staff (staffid , Staffname , Empid)
It is just example
how can i update Empname whose staffid =1 accor to staffid)
using Join Conditions :- Pls help me out ..
or
how to update data using JOIN Conditions
Yaman
View 2 Replies
View Related
Mar 13, 2006
I have a table with date values that are currently null.
I've created a query that identifies the dates that should go into the date field for the table. I'll call the table "shipping" and the SQL Query, "query".
query has the following fields:
CN int
CSN int
shipdate datetime
shipping has the following fields that are relavent:
CN int
CSN int
shipped_date datetime
I want to update shipping, setting shipping.shipped_date=query.shipdate
where query.cn=shipping.cn and query.csn=shipping.csn
How do I word it to get a proper update using query analyzer?
View 3 Replies
View Related
Jul 23, 2007
Looking to concat. two fields (w/o having to go to external scripting) in the process of an update that's running through a join.
Currently (to get events that cross today and update from the 'event' table to the current/daily table):
UPDATE tblEmployee SET tblEmployee.Status = tblFuture.Status
FROM tblFuture INNER JOIN tblEmployee
ON tblFuture.EmpID = tblEmployee.EmpID
WHERE DateDiff(DAY,GETDATE(),tblFuture.Start_Date) <= 0 AND DateDiff(DAY,GETDATE(),tblFuture.End_Date) >= 0
But I want to be able to set:
tblEmployee.Status = tblFuture.Status + tblFuture.Remarks ...
That doesn't work.
I've searched a bit here and there, but nothing successful thus far. Kind of new to SQL Server (not that I've done this in Oracle or MySQL). Anyhoo ... any ideas?
TIA!
View 6 Replies
View Related
Aug 27, 2007
Here is my update statements which doesn't work, can you show me an example or provide a hint.
thanks
update property
inner join propinv on propinvid=propertyinvid
set property.lotsize='100'
where property.lotsize <> '' and property.lotize is not null
Thank you
View 9 Replies
View Related
Mar 4, 2008
I have the table tbOld with columns as fieldID (PK), Class, Name, other1, other2, other3 and tbNew with the same structure (fieldID (PK), Class, Name, other1, other2, other3).
Most Class-Name combinations are the same in both tables but have different FieldID, others are only in the 'tbNew' and absent in the tbOld, some are only in the 'tbOld' but we are not interested in those.
We need to update data in tbNew for columns other1, other2, other3 and make them the same as in tbOld where possible (where class-Name combination is present in the tbOld)
The update statement based on the join on two columns does not work (both columns are not PK, as FieldID is PK in each table, but we can't join on it)
Any help is highly appreciated.
View 2 Replies
View Related
Mar 12, 2008
Hey guys,
Up to this point I've been dealing with mostly select statements but time has come, and I need to do an update. Basically I'm not sure how to structure the query.
I'd like to update the field "new_applicationreceived" to the value of "new_lastcontact" based off the results of the following select query:
select new_lastcontact from lead
LEFT JOIN StringMap ON Lead.SalesStageCode = StringMap.AttributeValue
AND StringMap.AttributeName = 'SalesStageCode'
AND StringMap.ObjectTypeCode = 4
where new_applicationreceived is null
and lead.salesstagecode = 5
I'd really appreciate your help - I'm very worried about messing up the data and don't want to screw something up.
View 2 Replies
View Related
Oct 29, 2007
Team,
Our product was written against an older version of SQL and the non-ANSI standard join operators (*= and =*). Our group is now looking to move our database to SQL Server 2005.
We are now faced with updating a lot of legacy queries that use this method of joins and I was hoping for a shove in the right direction.
I have run across a query similar to the following:
SELECT <many fields go here>
FROM a, b, c, d, e, f, g, h
WHERE a.fld1 *= d.fld1
AND e.fld3 *= h.fld3
The query, as it is with the non-ANSI join operators, returns a single record. For the life of me, I cannot figure out how to format the FROM statement to create the LEFT OUTER JOIN statement(s) to get the query to work.
The SQL Server 2005 books online seems to be lacking in the area of examples where you have multiple joins using different tables; they focus on one table joining against multiple tables instead.
Anyone have any reading suggestions or other ideas on how to get this to work?
Thanks.
Richard
View 13 Replies
View Related
Mar 31, 2008
Hi
Please some one help me with this
I have a storedproc
ALTER PROCEDURE [dbo].[usp_updateCustomerList]
(@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Address1 varchar(35),
@Address2 varchar(35),
@Address3 varchar(35),
@Address4 varchar(35),
@OrderAmount money,
@OrderStatus varchar(6))
as
UPDATE OrderDetails SET Customer.FirstName=@FirstName,Customer.LastName=@LastName,Customer.Address1=@Address1,Custome.Address2=@Address2,Customer.Address3=@Address3,Customer.Address4=@Address4,
OrderDetails.OrderAmount=@OrderAmount,OrderDetails.Status=@Status
From OrderDetails
INNER JOIN Customer
ON OrderDetails.CustomerNumber = Customer.CustomerNumber
where OrderDetails.CustomerID = @CustomerID
When iam executing the stored procedure iam getting error
The multi-part identifier "Customer.FirstName" could not be bound.
Please someone help me with this.
I want to update two tables so ia joining the two tables with customer number which in both the tables.
and customerID is only in OrderDetails table.
so someone please help me with this.
thanks
Ramya
View 6 Replies
View Related
Sep 21, 2007
update T_INS_GEN_QT_DETAILS set
IGQD_HYPOTHECATED=
(
select IGPD_HYPOTHECATED from T_INS_GEN_PL_DETAILS,T_INS_GEN_QT_DETAILS,T_INS_GEN_PL_HEADER
where
IGQD_QT_NO=IGPH_FROM_QT_NO
and
IGPH_PL_NO=IGPD_PL_NO
),
IGQD_HYP_COMPANY=
(select IGPD_HYP_COMPANY from T_INS_GEN_PL_DETAILS,T_INS_GEN_QT_DETAILS,
T_INS_GEN_PL_HEADER where IGQD_QT_NO=IGPH_FROM_QT_NO and
IGPH_PL_NO=IGPD_PL_NO )
When i use this update statement .
Msg 512, Level 16, State 1, Line 1
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.
(0 row(s) affected)
how to solve it ?
View 4 Replies
View Related
Sep 1, 2006
I have a join between two tables. I need to updated column a from table a with column a from table b. How do I do that with a set statement?
View 5 Replies
View Related
Dec 21, 2006
hi,
this are my tables...
student
stud_num
pk2
pk3
stud_group
1234
11
22
1
2147
88
66
2
2222
22
22
2
5432
55
44
1
9876
99
77
1
student1
stud_num
pk2
pk3
age
grade
pass
1234
11
22
21
77
0
5432
55
44
23
90
1
9876
99
77
23
90
1
student2
stud_num
pk2
pk3
age
grade
pass
2147
88
66
18
78
0
2222
22
22
15
90
1
and this is what I want to retrieve from these 3 tables
stud_num
pk2
pk3
age
5432
55
44
23
9876
99
77
23
2222
22
22
15
this my query....
SELECT student.stud_num, student.pk2, student.pk3, student1.age, student2.age AS Expr1FROM student INNER JOIN student1 ON student.stud_num = student1.stud_num AND student.pk2 = student1.pk2 AND student.pk3 = student1.pk3 INNER JOIN student2 ON student.stud_num = student2.stud_num AND student.pk2 = student2.pk2 AND student.pk3 = student2.pk3WHERE (student1.grade = '90') AND (student1.pass = '1') AND (student2.grade = '90') AND (student2.pass = '1')
however...it doesnt return any results...
can anyone correct my code?
thanx!
Sheila
View 5 Replies
View Related
