Update statement with select to another table

Question

SELECT rollID 
FROM [dbo].[dbo_Roll] a
  INNER JOIN [evt_Building Permits] b
    ON LEFT(a.rollnumber, LEN(a.rollnumber)-4) = b.rollnumber COLLATE DATABASE_DEFAULT

And I want to update the result to this table [evt_Building Permits].roll_id = [dbo].[dbo_Roll].rollID?

How can I attain this?

shA.t · Accepted Answer · 2015-05-17 11:12:02Z

1

Try this

UPDATE b
SET b.roll_id = a.rollID
FROM 
    [dbo].[dbo_Roll] a
  INNER JOIN 
    [evt_Building Permits] b
  ON LEFT(a.rollnumber, LEN(a.rollnumber)-4) = b.rollnumber COLLATE DATABASE_DEFAULT

edited May 17, 2015 at 11:12

answered Apr 17, 2015 at 13:23

shA.t

17k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sean Lange · Accepted Answer · 2015-04-17 13:22:55Z

0

Something like this.

update b
set rollID = a.rollID
from [dbo].[dbo_Roll] a
inner join [evt_Building Permits] b
on LEFT(a.rollnumber, LEN(a.rollnumber)-4) = b.rollnumber COLLATE DATABASE_DEFAULT

answered Apr 17, 2015 at 13:22

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Comments

philip.yuile · Accepted Answer · 2015-04-17 13:25:06Z

0

Try this syntax:

update b
set b.roll_id = a.rollID
from [dbo].[dbo_Roll] a
inner join [evt_Building Permits] b
on LEFT(a.rollnumber, LEN(a.rollnumber)-4) = b.rollnumber COLLATE DATABASE_DEFAULT

answered Apr 17, 2015 at 13:25

philip.yuile

4864 silver badges6 bronze badges

Collectives™ on Stack Overflow

Update statement with select to another table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related