update join query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update join query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-14 : 13:25:57

I have a query

SELECT tabled. * , tablep. *
FROM tabled, tablep
WHERE tabled.id=5 =tablep.id=5

how can cahnge this to be an update query -- meaning updating the results of teh above joined query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 13:32:02

Which table do you want to update and what exactly do you want to update and with what values?

Here's how to update the tabled table:

UPDATE d
SET SomeColumn = ...
FROM tabled d
INNER JOIN tablep
ON d.id = p.id
WHERE d.id = 5

Also the SELECT statement that you posted is invalid T-SQL syntax.

Tara Kizer

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-14 : 13:53:58

can you help me with what's wrong with the below (in mysql) the select without the update works

UPDATE orders SET amount_paid = amount WHERE orders_id IN (

SELECT orders.orders_id
FROM customer, orders
WHERE (
customer.contacts_id_primary = orders.contacts_id_primary
)
AND (
customer.event = '1'
AND orders.event =1
)
AND (
customer.amount = orders.amount
)
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-14 : 14:29:57

You need to look at the example that I provided in my last post and use that as a template for when you need to join two tables together in an UPDATE statement. Your posted query doesn't attempt to use it.

UPDATE o
SET amount_paid = c.amount
FROM orders o
INNER JOIN customer c
ON o.contacts_id_primary = c.contacts_id_primary AND c.amount = o.amount
WHERE c.event = '1' AND o.event = '1'

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 23:40:52

1 This is SQL Server Forum
2 I dont think Update with inner join is supported at MySQL
3 This is your third question on Mysql
4 You should post at www.DBForums.com or www.MYSQL.com as I suggested in other topics

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources