CODEWITHSUNDEEP
sql
user2781018
user2781018

Reputation:

Update Data on One Table Based on Another - SQL

I have two tables.

One is SUPPLIER with SupplierID as its PK and it has a State attribute.

The other is SUPPLIEDPART. It has a UnitCost attribute. SupplierID is a FK.

I'm trying to update the UnitCost based on whether or not the Supplier is located in the state of Texas. I have tried multiple queries, but they always fail. Here are some of the latest attempts. Any help would be appreciated. I have attached a picture of the relationship between the two tables if that will help. 

INSERT INTO SUPPLIEDPART (UnitCost)
  SELECT UnitCost * 1.1
  FROM SUPPLIEDPART SP INNER JOIN SUPPLIER S
  ON SP.SupplierID = S.SupplierID
  WHERE State = 'TX';

----This one results in a "cannot insert nulls into brewer34.suppliedpart.supplierid

UPDATE SUPPLIEDPART
  SET UnitCost = UnitCost * 1.1
  WHERE SupplierID = (SELECT SupplierID
                     FROM SUPPLIER
                     WHERE State = 'TX')
  GROUP BY PartNumber;

----This query results in a command not properly ended----

enter image description here

Upvotes: 0

Views: 63

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

On most DBMS you can do:

UPDATE SUPPLIEDPART AS a
INNER JOIN SUPPLIER AS b ON a.SupplierID = b.SupplierID
SET a.UnitCost = a.UnitCost * 1.1
WHERE  b.State = 'TX';

Upvotes: 0

D Stanley
D Stanley

Reputation: 152501

I think you were close:

UPDATE SUPPLIEDPART
SET UnitCost = UnitCost * 1.1
WHERE SupplierID IN (SELECT SupplierID    -- <-- IN instead of =
                 FROM SUPPLIER
                 WHERE State = 'TX')
-- no GROUP BY

Upvotes: 1

Related Questions