jeeperscreepers jeeperscreepers - 1 month ago 10
SQL Question

Update values on one table from another table when criteria is met

I would like to update one table in my db depending on a value from another table.
The 2 tables are:

QUOTES(
QUOTE_ID,
APPROVAL
)

PRODUCTS(
QUOTE_ID_FK,
LINE_APPROVAL
)


I need to update all
PRODUCTS.LINE_APPROVAL
to
'Y'
where the
QUOTES.APPROVAL = 'Y'


I have tried something like this but fails as I don't have the syntax quite right but hopefully you can see what I'm trying to achieve:

UPDATE
PRODUCTS
SET
LINE_APPROVAL = 'Y'
FROM
QUOTES,
PRODUCTS
WHERE
PRODUCTS.QUOTE_ID = QUOTES.QUOTE_ID_FK
AND
QUOTES.APPROVAL = 'Y'


Any help would be appreciated.

Answer

this query will, update all products which have a value in PQUOTE_ID that appears in the quotes table column Quote_ID_FK (and approval is Y).

UPDATE 
    PRODUCTS
SET 
    LINE_APPROVAL = 'Y'
WHERE 
    PQUOTE_ID IN 
       (SELECT
           QUOTE_ID_FK 
        FROM
           QUOTES
        WHERE
           APPROVAL = 'Y')