CODEWITHSUNDEEP
sqlsql-server-2012
Jin Imuno
Jin Imuno

Reputation: 11

SQL code for updating a column where the WHERE condition is from another table

In SQL Server 2012, I have two tables: WINE_TYPE and WINE.

WINE_TYPE is the parent table, and wtCode is the primary key (foreign key in WINE).

I'm trying to execute code that will update the price of the wine (winePrice) by 50 cents, or 0.50. The catch is... my WHERE condition is from the parent table (I only need the wines that are made by the lead Amanda McPhee (wtLeadFirst='Amanda', wtLeadLast='McPhee') to be increased by 50 cents).

Inner join doesn't seem to work here. Help?

Upvotes: 1

Views: 58

Answers (2)

dotnetom
dotnetom

Reputation: 24901

You can use UPDATE ... FROM syntax to update table with JOIN condition:

UPDATE WINE
SET WINE.WinePrice = WINE.WinePrice + 0.5
FROM
    WINE
INNER JOIN
    WINE_TYPE ON WINE.wtCode = WINE_TYPE.wtCode
WHERE
    WINE_TYPE.wtLeadFirst='Amanda' AND WINE_TYPE.wtLeadLast='McPhee'

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93724

Update + Exists

Use Exists operator to check the existence of wtLeadFirst='Amanda' and wtLeadLast='McPhee' in parent table

update W
Set Wineprice = x + Wineprice 
from Wine W 
where exists (select 1 
              from wine_type WT 
              where W.wtCode =WT.code 
              and Wt.wtLeadFirst='Amanda' 
              and Wt.wtLeadLast='McPhee' )

Upvotes: 1

Related Questions