Geeks With Blogs
faizan ahmad Usually the things which were not a straight Google

I had to update value from a staging table( this might be a temp table ) into another database table. Here is how I did it using a inner join in the Update statement.

UPDATE

Table1

SET

Table1

. Field1 = StagingTable . Field1

FROM

Table1

INNER JOIN StagingTable

ON Table1 . Field2 = StagingTable . Field2

WHERE

StagingTable . Field3 IS NOT NULL

---------------------------------------------------------------------

Same way we can use JOINs in delete statements

Syntax for join in Delete is a bit different e.g.

DELETE

TB1

FROM

Table1 TB1 INNERJOIN

StagingTable

ON Table1.Field2 = StagingTable.Field2

Update 1: Following is the  MySQL syntax (Thanks Evan)

UPDATE

table1

INNER

JOIN projects

ON

table1. field1 = table2.field2

SET

table1.field3 = table2.field4

 

Update 2:

 This did not work for me in Oracle, had to use sub queries


Update3: Teradata

Following is the teradata way:

UPDATE TargetTable
FROM SourceTable
SET TargetTableField = SourceTable.SourceTableField
WHERE  TargetTable.Field = SourceTable.Field; -- join on expression will come here
Posted on Monday, January 5, 2009 11:33 AM | Back to top


Comments on this post: join in SQL update statement

# re: join in SQL update statement
Requesting Gravatar...
Thanks - This is just what I needed.
Left by Linda on Jul 28, 2009 11:44 AM

# re: join in SQL update statement
Requesting Gravatar...
Just what I needed.

Thanks
Left by Phil on Jul 29, 2009 8:35 PM

# re: join in SQL update statement
Requesting Gravatar...
Teşekkür edrim. Çok işime yaradı..
(Thanks. This help me too much)
Left by Sermin on Jul 30, 2009 3:14 AM

# re: join in SQL update statement
Requesting Gravatar...
I hate to say it, but this doesn't work. Using the exact same format as your example, the error "Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'From'." is displayed.

I want to update the field HHTable.Value and change it to the value in field Table2.NewValue if the field HHTable.Keyfield is equal to the field Table2.Row.

Any suggestions on what I need to do differently to this query above?
Left by Matt on Sep 28, 2009 10:47 AM

# re: join in SQL update statement
Requesting Gravatar...
Thanks, just what I needed.
Left by Kadhim on Nov 04, 2009 5:16 AM

# re: join in SQL update statement
Requesting Gravatar...
Had same problem as Matt: sytanx problem near FROM, using MYSQL.

Fix:

UPDATE table1 INNER JOIN projects ON
table1. field1 = table2.field2

SET table1.field3 = table2.field4

Thanks for the guidance!
Left by Evan on Nov 30, 2009 11:24 AM

# re: join in SQL update statement
Requesting Gravatar...
Excellent! worked perfectly. Thanks for the help!
Left by Ian on Dec 08, 2009 10:30 AM

# re: join in SQL update statement
Requesting Gravatar...
You saved my day thank you ...

I was afraid to miss General Zero:Hour game With my friends ... While trying to write an Application to do this job ...
But big thanks for you ...
I finish my Job with your statement in a few minutes ....
And 0 Mistakes ... :)

Kind regards ...
Left by Stark DaFixzer on Jan 28, 2010 2:57 PM

# re: join in SQL update statement
Requesting Gravatar...
Just what i needed.

Thanks!!!
Left by Danilo Garcia on Mar 16, 2010 12:38 PM

# re: join in SQL update statement
Requesting Gravatar...
thank you so much, you saved my life!"!!
Left by LMC on Apr 22, 2010 8:53 AM

# re: join in SQL update statement
Requesting Gravatar...
This isn't working with multiple join clouses!
Left by galin on Jun 02, 2010 7:29 AM

# re: join in SQL update statement
Requesting Gravatar...
it should be like that

UPDATE product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
SET pp.price = pp.price * 0.8
WHERE p.dateCreated < '2004-01-01'
Left by galin on Jun 02, 2010 7:30 AM

# re: join in SQL update statement
Requesting Gravatar...
Excellent!!!!!
it works
thank you every body, especially Evan
Left by Noiiii on Jun 03, 2010 11:13 AM

# re: join in SQL update statement
Requesting Gravatar...
sir i want to update by joining two tables.
but one field is in one table and other is in 2nd. suppose i want to update origin field i want to set origin ='PAKISTAN'
where clasue related to 2nd table.
please help
Left by YASIR on Jul 12, 2010 2:16 AM

# re: join in SQL update statement
Requesting Gravatar...
Thanks, yep its work !!
Left by Ajay on Aug 21, 2010 2:33 AM

# re: join in SQL update statement
Requesting Gravatar...
(With all due respect:) Lighten up, Chris!
Faizan's code worked perfectly for me, Evan's did NOT. My experience was exactly the OPPOSITE of yours & Evan's etc.
I arrived here with my code [virtually] identical to the code that Evan posted, yet it produced the error:"Incorrect syntax near the keyword 'INNER'
However, when I changed it to [virtually] the exact same code that Faizan posted originally it worked perfectly.
Let me suggest that we might be comparing "apples and oranges". I started with my [working] query in MSAccess (03), where I use that handy query builder.
Then I moved the query to MS SQL Server 2005, where the same query failed! Get it?
The query parsing was different, even within Microsofts own products! (what a surprise).
I've not read in these posts anything about what version of SQL environment posters are using.
Are we really assuming that all versions of SQL will work the same (there must be over two dozen just in MS, then Express editions, MSAccess, etc.). Someone might even say they are using "ANSI-STANDARD SQL" in Oracle - I think we can agree their is no standard for some SQL details such as this and a bit of trial and error is in order.
Left by Hummm on Sep 04, 2010 1:28 PM

# re: join in SQL update statement
Requesting Gravatar...
My Bad, I need to correct one error in my post above, I just (finally) noticed that Evan DID say he was using "MySQL"! That could be the issue, unless I'm really numb (possible, jury's still out), I did not get any clues if this forum was "SQL Server", "MySQL" etc. Except for Evan reference, I've not seen any reference that indicates such. It is well possible that Faizan and I and a few others are using SQL Server, in one flavor or another and Faizan original code was "happy", while Evan & Chris and a few others are using MySQL (or MSAccess) and we're neglecting to identify and hence be aware of the differences between the two SQL syntax differences/requirements.
Left by Hummm on Sep 04, 2010 1:35 PM

# re: join in SQL update statement
Requesting Gravatar...
THis does not work for me (MS SQL 2005). Somehow the query takes ages to complete.

I have a table and a view, I wish to update a value in the table according to the contents of the view.

I have the following query:
UPDATE Vacancies
SET Vacancies.IsBlind = vwVacancyIsBlind.IsBlindVacancy
FROM Vacancies INNER JOIN vwVacancyIsBlind
ON Vacancies.VacancyID = vwVacancyIsBlind.VacancyID

Where vwVacancyIsBlind is a view that outputs a vacancy and whether it is blind or not (based on older logic, which is to be replaced with a simple bit maintained in the vacancy).

What am I doing wrong?
Left by Michael on Sep 16, 2010 7:30 AM

# re: join in SQL update statement
Requesting Gravatar...
Never mind that last comment. It just seemed to do just that: take ages. After a couple of minutes it produced the required result.

Thanks!
Left by Michael on Sep 16, 2010 7:32 AM

# re: join in SQL update statement
Requesting Gravatar...
i have a problem computing the date difference plz help:
table 1 has uid and date like
uid:1
date:2009-09-08

uid:2
date:2009-08-09

uid:1
date:2009-08-09

table 2 has all uid as primary key

i need to compute the date difference with respect to the uid as
uid:1
date:2009-09-08
date:2009-08-09

and add it into the table 2 with uid=1
Left by lalitha on Oct 24, 2010 11:29 PM

# re: join in SQL update statement
Requesting Gravatar...
Thanks for the valuable information. Your post saved me some time.
Left by Matt on Dec 06, 2010 12:22 PM

# re: join in SQL update statement
Requesting Gravatar...
Worked for me, thanks!
Left by D on Feb 01, 2011 1:03 AM

# re: join in SQL update statement
Requesting Gravatar...
I'm trying to do this same thing but I'm using SQLPlus with an Oracle 10G. I'm getting an error with each variation of the above examples. Does anyone know how to do this same thing in Oracle? thanks
Left by deb on Feb 09, 2011 9:50 AM

# re: join in SQL update statement
Requesting Gravatar...
Great, saved me also some time
Left by Garfield on Feb 23, 2011 9:22 AM

# re: join in SQL update statement
Requesting Gravatar...
Great. Thanks!!

It works for me.
Left by Manish Gokhare on Apr 15, 2011 9:38 PM

# re: join in SQL update statement
Requesting Gravatar...
Thanks , the info was very helpful
Left by Learner on Jun 20, 2011 7:11 AM

# re: join in SQL update statement
Requesting Gravatar...
Ahh Danke! Short and to the point.
Left by Pete on Jul 15, 2011 5:50 AM

# re: join in SQL update statement
Requesting Gravatar...
It help me alot
Left by candy on Aug 12, 2011 6:35 AM

# re: join in SQL update statement
Requesting Gravatar...
Nice example. That was what I was after.
Left by Emil on Oct 09, 2011 4:19 AM

# re: join in SQL update statement
Requesting Gravatar...
Thanks a lot, really useful. I use this page a lot!
Left by Tom B on Oct 19, 2011 11:28 AM

# re: join in SQL update statement
Requesting Gravatar...
Hi,
I tried about query but it did not work. I found another syntax that works::


UPDATE Table1, Table2 SET Table1.Field1 = StagingTable.Field1 WHERE Table1.Field2 = StagingTable.Field2


Basically, use both tables names after the UPDATE keyword.
Left by Janak on Nov 29, 2011 4:28 AM

# re: join in SQL update statement
Requesting Gravatar...
"PS: This syntax works in MS Sql Server, For MySQL refer Evan's comments below."

WHAT COMMENTS I NEED TO KNOW MYSQL SYNTAX WHO IS EVAN WHERE IS THE COMMENT OH GOD
Left by your name on Dec 10, 2011 1:19 PM

# re: join in SQL update statement
Requesting Gravatar...
This help me too much.. thanks...
Left by Rony T Sam on Dec 30, 2011 3:49 AM

# re: join in SQL update statement
Requesting Gravatar...
A Good Example:

UPDATE Pharmacy_tblStockItems
SET Pharmacy_tblStockItems.dblTotalQuantity = (Pharmacy_tblStockItems.dblTotalQuantity - (Pharmacy_tblPurchaseInvoiceDetail.intFreeQuantity + Pharmacy_tblPurchaseInvoiceDetail.intQuantity)),
Pharmacy_tblStockItems.dblFreeQuantity = (Pharmacy_tblStockItems.dblFreeQuantity - Pharmacy_tblPurchaseInvoiceDetail.intFreeQuantity) ,
Pharmacy_tblStockItems.dblQuantity = ( Pharmacy_tblStockItems.dblQuantity - Pharmacy_tblPurchaseInvoiceDetail.intQuantity)
FROM Pharmacy_tblStockItems INNER JOIN Pharmacy_tblPurchaseInvoiceDetail ON (Pharmacy_tblStockItems.intProductMasterDetailsId = Pharmacy_tblPurchaseInvoiceDetail.intProductDetailId)
WHERE Pharmacy_tblPurchaseInvoiceDetail.intPurchaseInvoiceMasterId = 6;
Left by Rony T Sam on Dec 30, 2011 3:51 AM

# re: join in SQL update statement
Requesting Gravatar...
Very valuable information, thank you.
Left by YV on Feb 08, 2012 3:13 AM

# re: join in SQL update statement
Requesting Gravatar...
Thank for this...saved me!!
Left by KT on Feb 15, 2012 4:10 PM

# re: join in SQL update statement
Requesting Gravatar...
Thank you very very much. You saved the day
Left by Abdullah on Mar 15, 2012 4:35 PM

# re: join in SQL update statement
Requesting Gravatar...
Perfect! Applied in 5 minutes to get the desired result. Thanks for making my day.
Left by Lee on Apr 26, 2012 5:52 PM

# re: join in SQL update statement
Requesting Gravatar...
Thanks =D
Left by Sue on Jun 11, 2012 12:19 AM

# re: join in SQL update statement
Requesting Gravatar...
thanks you very much, simple and effective
Left by Alessio on Jun 22, 2012 4:57 AM

# FROM clause in UPDATE
Requesting Gravatar...
Useful tip! Thanks.
Left by Andrei Rinea on Aug 17, 2012 8:37 AM

# re: join in SQL update statement
Requesting Gravatar...
Can use the below one:

update table1
set table1.FIELD1 = table2.FIELD2
from table2
where table2.FIELD3 = table1.FIELD4
Left by JAI B. on Sep 27, 2012 3:19 AM

# re: join in SQL update statement
Requesting Gravatar...
nice 1
Left by blah blah on Mar 19, 2013 3:16 AM

# re: join in SQL update statement
Requesting Gravatar...
nice 1 good
Left by blah blah on Mar 19, 2013 3:16 AM

# re: join in SQL update statement
Requesting Gravatar...
Thanks a lot!
Left by Juli on Apr 16, 2013 7:26 AM

# re: join in SQL update statement
Requesting Gravatar...
Jai B.'s code worked for me! many thanks!
Left by ACRL on Jul 09, 2013 6:22 PM

# re: join in SQL update statement
Requesting Gravatar...
hai the brief example can give the upadate and delete of join qurey.
Left by rani on Oct 16, 2013 4:21 AM

# re: join in SQL update statement
Requesting Gravatar...
Thank for you post. It works good for me!
Left by Snake on Dec 11, 2013 3:39 AM

# re: join in SQL update statement
Requesting Gravatar...
this is my query :
Select whstocklink,whqtyonhand,stocklink,qty_on_hand from whsestk
inner join stkitem
ON
WhseStk.Whstocklink = Stkitem.Stocklink

i want to update the stkitem quantity on hand to be identical to the ones in whsestk.

ANY HELP PLEASE!!!
Left by Junky Fun on Feb 03, 2014 4:01 AM

# Thanks so much
Requesting Gravatar...
Thanks, this tip helps me a lot.
Left by Binh Nguyen Thanh on Jul 29, 2014 7:02 PM

# re: join in SQL update statement
Requesting Gravatar...
I visited few sites but did not able to resolve my purpose but this is very informative , generalized format and useful. Thanks a lot. Being a beginner i was able to use joins and updates together. Happy to have successful execution. :)
Left by khushbu gupta on Jan 06, 2015 4:36 AM

# re: join in SQL update statement
Requesting Gravatar...
getting an error for this qyr, pls help

update Target A
from source B
set A.cons_prfl_ky = B.cons_prfl_ky
where A.eml_ad = B.eml_ad
Left by Kumar on Mar 24, 2015 1:42 PM

Your comment:
 (will show your gravatar)


Copyright © faizanahmad | Powered by: GeeksWithBlogs.net | Join free