locked
update table RRS feed

  • Question

  • User-804681621 posted
    I have select id,name record by joining different, and I would like to update the selected record in another table based on the selected if and name to update.

    How to do that?

    Below is my method not not work.

    Update table1 set col=a.value from (select * from cust where status='a') c inner join detail d on c.cust=d.cust)

    What is wrong? I can't update the record I want to update.
    Thursday, January 21, 2016 5:56 AM

All replies

  • User364663285 posted

    "table1" is wrong and what is it?

    Try the same way like

    UPDATE ProductReviews AS R 
       INNER JOIN products AS P 
           ON R.pid = P.id 
    SET R.status = '0' 
    WHERE R.id = '17190' 
      AND P.shopkeeper = '89137'

    Thursday, January 21, 2016 5:59 AM
  • User-1716253493 posted
    INSERT INTO TABLE1 (col1, col2) SELECT col1,col2 from ... where ...
    Thursday, January 21, 2016 6:11 AM
  • User1724605321 posted

    Hi pn_ng,

    Try to use structure like:

    UPDATE
        Table_A
    SET
        Table_A.col1 = Table_B.col1,
        Table_A.col2 = Table_B.col2
    FROM
        Some_Table Table_A
    INNER JOIN
        Other_Table Table_B
    ON
        Table_A.id = Table_B.id
    WHERE
        Table_A.col3 = 'cool'

    From this thread: http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server

    Best Regards,

    Nan Yu

    Thursday, January 21, 2016 6:39 AM
  • User-698989805 posted

    You need to join the tables like below:

    UPDATE Table1 SET Col2 = t2.Col2, Col3 = t2.Col3 FROM Table1 t1 INNER JOIN Table2 t2 
    ON t1.Col1 = t2.Col1 WHERE t1.Col1 IN (22, 44) //22, 44 - Column ID

    May be the sub-query causes the problem.

    Thursday, January 21, 2016 8:00 AM
  • User-804681621 posted
    Anyone help
    Thursday, January 21, 2016 8:37 AM
  • User77042963 posted
    Merge table1 t1
    Using (select c.id, d.value from cust c inner join detail d on c.cust=d.cust  where c.status='a')  src 
    on t1.id=src.id
    
    When matched then
    Update  set col=src.value;

    Thursday, January 21, 2016 2:59 PM