How to update using JOIN with another table

Question

I have a problem my select worked fine without update and my update worked fine without select but when i combine both my code don't do any reaction to me ( don't work - without a error )

UPDATE Table1 
SET 
l2p= 0.207 * (SELECT DUM FROM Table2 WHERE [no] < 1389)
WHERE [no] < 1389

thanks to see and any help

I am surprise you are not getting errors. You should get subquery returning more than 1 row error — DevelopmentIsMyPassion, Sep 29 '13 at 19:17
possible duplicate of [How can I do an UPDATE statement with JOIN in SQL?](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql) — Yosi Dahari, Sep 29 '13 at 19:24

Christian Phillips · Accepted Answer · 2013-09-29T19:35:31.503

1

Try declaring a variable to hold the result of the SELECT statement.

DECLARE @DUM INT
SET @DUM = (SELECT ISNULL(DUM, 1) FROM Table2 WHERE [no] < 1389)

UPDATE Table1 
SET 
l2p= 0.207 * @DUM
 WHERE [no] < 1389

If you have multiple records, this will not work, you would need to join against Table2...

UPDATE t
SET t.l2p = 0.207 * ISNULL(t2.DUM, 1)
FROM Table1 t INNER JOIN Table2 t2 
     ON t.[no] = t2.[no] 
WHERE t.[no] < 1389

You should really use a stored procedure, as below...

CREATE PROCEDURE [dbo].[sprocName] 
   @No INT
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE t
    SET t.l2p = 0.207 * ISNULL(t2.DUM, 1)
    FROM Table1 t INNER JOIN Table2 t2 
         ON t.[no] = t2.[no] 
    WHERE t.[no] < @No
END

To run the stored procedure, use EXEC sprocName @No=1389

edited Sep 29 '13 at 19:35

answered Sep 29 '13 at 19:12

Christian Phillips

18,399
8
53
82

thanks to helping i dont use JOINT befor it ( so i must try use JOINT thanks alot ) you help full – ehsan wwe Sep 29 '13 at 19:26
Is this a one-off update, or is that `[no]` likely to change? if so, you should use a stored procedure – Christian Phillips Sep 29 '13 at 19:31

Yosi Dahari · Answer 2 · 2013-09-29T19:20:08.620

1

You should use JOIN:

UPDATE Table1
SET Table1.l2p = 0.207 * Table2.DUM
FROM Table1 JOIN Table2 ON Table1.[no] = Table2.[no] 
WHERE Table1.[no] < 1389

edited Sep 29 '13 at 19:20

answered Sep 29 '13 at 19:15

Yosi Dahari

6,794
5
24
44

Then you should learn, this is what you need, don't just look for a temporary solution. – Yosi Dahari Sep 29 '13 at 19:21
i dont see any error but my sellect dont work like first time – ehsan wwe Sep 29 '13 at 20:25

How to update using JOIN with another table

2 Answers2