faizan ahmad

Usually the things which were not a straight Google

  Home  |   Contact  |   Syndication    |   Login
  17 Posts | 0 Stories | 75 Comments | 0 Trackbacks

News

Archives

Post Categories

.NET

ASP.NET

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

Feedback

# re: join in SQL update statement 7/28/2009 11:44 AM Linda
Thanks - This is just what I needed.

# re: join in SQL update statement 7/29/2009 8:35 PM Phil
Just what I needed.

Thanks

# re: join in SQL update statement 7/30/2009 3:14 AM Sermin
Teşekkür edrim. Çok işime yaradı..
(Thanks. This help me too much)

# re: join in SQL update statement 9/28/2009 10:47 AM Matt
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?

# re: join in SQL update statement 11/4/2009 5:16 AM Kadhim
Thanks, just what I needed.

# re: join in SQL update statement 11/30/2009 11:24 AM Evan
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!

# re: join in SQL update statement 12/8/2009 10:30 AM Ian
Excellent! worked perfectly. Thanks for the help!

# re: join in SQL update statement 1/28/2010 2:57 PM Stark DaFixzer
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 ...


# re: join in SQL update statement 3/16/2010 12:38 PM Danilo Garcia
Just what i needed.

Thanks!!!

# re: join in SQL update statement 4/22/2010 8:53 AM LMC
thank you so much, you saved my life!"!!

# re: join in SQL update statement 6/2/2010 7:29 AM galin
This isn't working with multiple join clouses!

# re: join in SQL update statement 6/2/2010 7:30 AM galin
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'

# re: join in SQL update statement 6/3/2010 11:13 AM Noiiii
Excellent!!!!!
it works
thank you every body, especially Evan

# re: join in SQL update statement 7/12/2010 2:16 AM YASIR
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

# re: join in SQL update statement 8/21/2010 2:33 AM Ajay
Thanks, yep its work !!

# re: join in SQL update statement 9/4/2010 1:28 PM Hummm
(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.


# re: join in SQL update statement 9/4/2010 1:35 PM Hummm
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.

# re: join in SQL update statement 9/16/2010 7:30 AM Michael
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?

# re: join in SQL update statement 9/16/2010 7:32 AM Michael
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!

# re: join in SQL update statement 10/24/2010 11:29 PM lalitha
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

# re: join in SQL update statement 12/6/2010 12:22 PM Matt
Thanks for the valuable information. Your post saved me some time.

# re: join in SQL update statement 2/1/2011 1:03 AM D
Worked for me, thanks!

# re: join in SQL update statement 2/9/2011 9:50 AM deb
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

# re: join in SQL update statement 2/23/2011 9:22 AM Garfield
Great, saved me also some time

# re: join in SQL update statement 4/15/2011 9:38 PM Manish Gokhare
Great. Thanks!!

It works for me.

# re: join in SQL update statement 6/20/2011 7:11 AM Learner
Thanks , the info was very helpful

# re: join in SQL update statement 7/15/2011 5:50 AM Pete
Ahh Danke! Short and to the point.

# re: join in SQL update statement 8/12/2011 6:35 AM candy
It help me alot

# re: join in SQL update statement 10/9/2011 4:19 AM Emil
Nice example. That was what I was after.

# re: join in SQL update statement 10/19/2011 11:28 AM Tom B
Thanks a lot, really useful. I use this page a lot!

# re: join in SQL update statement 11/29/2011 4:28 AM Janak
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.

# re: join in SQL update statement 12/10/2011 1:19 PM your name
"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

# re: join in SQL update statement 12/30/2011 3:49 AM Rony T Sam
This help me too much.. thanks...

# re: join in SQL update statement 12/30/2011 3:51 AM Rony T Sam
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;


# re: join in SQL update statement 2/8/2012 3:13 AM YV
Very valuable information, thank you.

# re: join in SQL update statement 2/15/2012 4:10 PM KT
Thank for this...saved me!!

# re: join in SQL update statement 3/15/2012 4:35 PM Abdullah
Thank you very very much. You saved the day

# re: join in SQL update statement 4/26/2012 5:52 PM Lee
Perfect! Applied in 5 minutes to get the desired result. Thanks for making my day.

# re: join in SQL update statement 6/11/2012 12:19 AM Sue
Thanks =D

# re: join in SQL update statement 6/22/2012 4:57 AM Alessio
thanks you very much, simple and effective

# FROM clause in UPDATE 8/17/2012 8:37 AM Andrei Rinea
Useful tip! Thanks.

# re: join in SQL update statement 9/27/2012 3:19 AM JAI B.
Can use the below one:

update table1
set table1.FIELD1 = table2.FIELD2
from table2
where table2.FIELD3 = table1.FIELD4

# re: join in SQL update statement 3/19/2013 3:16 AM blah blah
nice 1

# re: join in SQL update statement 3/19/2013 3:16 AM blah blah
nice 1 good

# re: join in SQL update statement 4/16/2013 7:26 AM Juli
Thanks a lot!

# re: join in SQL update statement 7/9/2013 6:22 PM ACRL
Jai B.'s code worked for me! many thanks!

# re: join in SQL update statement 10/16/2013 4:21 AM rani
hai the brief example can give the upadate and delete of join qurey.

# re: join in SQL update statement 12/11/2013 3:39 AM Snake
Thank for you post. It works good for me!

# re: join in SQL update statement 2/3/2014 4:01 AM Junky Fun
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!!!

# Thanks so much 7/29/2014 7:02 PM Binh Nguyen Thanh
Thanks, this tip helps me a lot.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: