UPDATE SQL for Multiple Records

Hi,

We know how to use SQL UPDATE commands (e.g. UPDATE SET Field='Value WHERE etc.)

However we are wanting to UPDATE many records on mass

e.g. UPDATE a table of multiple records and a unique primary key with values from another table with the same common unique primary key in several records

Is this at all possible, and if so can you post some code that work, ohterwise the only other option is to scan thru every record in Table A, get it's Primary Key and then update from the 2nd table. When we have 10,000 records that'll prove a bit typesome!!
StuartKAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
lluddenConnect With a Mentor Commented:
If TableA and TableB both have ID as a shared Key,

UPDATE TableA SET Field1 = Tableb.Field1, Field2 = TableB.Field2
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.ID
0
 
Patrick MatthewsConnect With a Mentor Commented:
Something like this:

UPDATE tableA
SET ColumnX = b.ColumnY
FROM tableA a INNER JOIN
    tableB b ON a.ID = b.ID

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Patrick MatthewsCommented:
slow fingers :)
0
 
StuartKAuthor Commented:
Three minutes later get a reply from three gurus!. What can I say!!! and the most perfect of answers from you all! Thanks to everyone. Only thing I can do is split between you.

Thank u so much guys!
0
 
StuartKAuthor Commented:
Thanks again!
0
 
StuartKAuthor Commented:
Can I ask one further question related to this. i know I've allocated points already. I'd be so grateful for your guidance! Been trying to figure out!

We want our TableB to be from a select statement. Is there anyway we can embed this SELECT statement  below in the above statement

SELECT     MAX(RenewalDate) AS MinRenewalDate, AlarmRef
FROM         dbo.Tbl_AlarmServiceAgreements
GROUP BY AlarmRef

This would save us the hassle of having to create a Temproary Table and using that table as TableB above.
0
 
Anthony PerkinsCommented:
UPDATE  a
SET     Col1 = b.MinRenewalDate
FROM    TableA a
        INNER JOIN (SELECT  MAX(RenewalDate) MinRenewalDate,
                            AlarmRef
                    FROM    dbo.Tbl_AlarmServiceAgreements
                    GROUP BY AlarmRef
                   ) b ON a.ID = b.AlarmRef
0
 
StuartKAuthor Commented:
acperkins: you a guru man!!

Was just asking the guys who posted previous and unfortunately I'd already allocated points, but seriously I thank you so much. Again that backs up my opinion of gurus on EE. They don't do it for gain or points, just to help helpless people like myself in distress! lol. Thank you so much!
0
All Courses

From novice to tech pro — start learning today.