Thanks - This is just what I needed.

Just what I needed.

Thanks

Teşekkür edrim. Çok işime yaradı..
(Thanks. This help me too much)

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?

Thanks, just what I needed.

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!

Excellent! worked perfectly. Thanks for the help!

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 ...

Just what i needed.

Thanks!!!

thank you so much, you saved my life!"!!

This isn't working with multiple join clouses!

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'

Excellent!!!!!
it works
thank you every body, especially Evan

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

Thanks, yep its work !!

(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.

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.

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?

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!

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

Thanks for the valuable information. Your post saved me some time.

Worked for me, thanks!

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

Great, saved me also some time

Great. Thanks!!

It works for me.

Thanks , the info was very helpful

Ahh Danke! Short and to the point.

It help me alot

Nice example. That was what I was after.

Thanks a lot, really useful. I use this page a lot!

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.

"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

This help me too much.. thanks...

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;

Very valuable information, thank you.

Thank for this...saved me!!

Thank you very very much. You saved the day

Perfect! Applied in 5 minutes to get the desired result. Thanks for making my day.

Thanks =D

thanks you very much, simple and effective

Useful tip! Thanks.

Can use the below one:

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

nice 1

nice 1 good

Thanks a lot!

Jai B.'s code worked for me! many thanks!

hai the brief example can give the upadate and delete of join qurey.

Thank for you post. It works good for me!

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, this tip helps me a lot.

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. :)

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