update query syntax

Question

update query syntax

Terry-380449

Default port

Points: 1458
More actions
March 20, 2008 at 11:28 am

#180221

Hello
I have a PRODUCTS table which contains 2 fields: old product code and new product code.
I also have a clients table, where the old code is stored.
I would like to update the clients table to change the old code to the new one in the PRODUCTS table.
What's the best way to write it?
Update Clients
set Clients.product = (get the new product code from the PRODUCTS)
where Clients.product = (get the old product code from the PRODUCTS)
Thanks for your help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398730 More actions · Answer 1

Terry (3/20/2008)
Hello
I have a PRODUCTS table which contains 2 fields: old product code and new product code.
I also have a clients table, where the old code is stored.
I would like to update the clients table to change the old code to the new one in the PRODUCTS table.
What's the best way to write it?
Update Clients
set Clients.product = (get the new product code from the PRODUCTS)
where Clients.product = (get the old product code from the PRODUCTS)
Thanks for your help

This is a sample from Northwind that'll do what you want

UPDATE dbo.Categories

SET CategoryName = p.ProductName

FROM dbo.Products p

INNER JOIN dbo.Categories c

ON p.CategoryID = c.CategoryID

WHERE c.CategoryName = 'Produce'

It's the UPDATE ... FROM that's the key.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

Try this. It uses a derived tables which are one of the powers of 2005.

UPDATE CLIENT

SET PRODUCT_CODE = b.NEW_CODE

FROM CLIENT, (SELECT OLD_CODE, NEW_CODE FROM PRODUCTS_CODE, CLIENT WHERE OLD_CODE = CLIENT.PRODUCT_CODE) AS B

WHERE PRODUCT_CODE = b.OLD_CODE

Let me know if this helps

Marvin Dillard
Senior Consultant
Claraview Inc

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

MD (3/20/2008)
Try this. It uses a derived tables which are one of the powers of 2005.
UPDATE CLIENT
SET PRODUCT_CODE = b.NEW_CODE
FROM CLIENT, (SELECT OLD_CODE, NEW_CODE FROM PRODUCTS_CODE, CLIENT WHERE OLD_CODE = CLIENT.PRODUCT_CODE) AS B
WHERE PRODUCT_CODE = b.OLD_CODE
Let me know if this helps

A little bit of extra code in the update query above. Here is a simplier version (plus it uses ANSI standard joins):

UPDATE dbo.CLIENT SET

PRODUCT_CODE = b.NEW_CODE

FROM

dbo.CLIENT c

inner join dbo.PRODUCTS_CODE b

on (c.PRODUCT_CODE = b.OLD_CODE)

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 4

Lynn

Sorry, I was too lazy to convert my code to ansi standard, and yes I know it should be. That's what I get for being lazy.

yes yours is cleaner

Thanks

Marvin Dillard
Senior Consultant
Claraview Inc

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Marvin,

The only reason I found that what I wrote was cleaner was I started to turn your derived table into a CTE and discovered the duplication in the query. CTE's do have a reason for existance even if they aren't used.

Lynn