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!!
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!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Patrick Matthews
slow fingers :)
ASKER
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!
Thank u so much guys!
ASKER
Thanks again!
ASKER
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_AlarmServiceAgreem
GROUP BY AlarmRef
This would save us the hassle of having to create a Temproary Table and using that table as TableB above.
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_AlarmServiceAgreem
GROUP BY AlarmRef
This would save us the hassle of having to create a Temproary Table and using that table as TableB above.
UPDATE a
SET Col1 = b.MinRenewalDate
FROM TableA a
INNER JOIN (SELECT MAX(RenewalDate) MinRenewalDate,
AlarmRef
FROM dbo.Tbl_AlarmServiceAgreem
GROUP BY AlarmRef
) b ON a.ID = b.AlarmRef
SET Col1 = b.MinRenewalDate
FROM TableA a
INNER JOIN (SELECT MAX(RenewalDate) MinRenewalDate,
AlarmRef
FROM dbo.Tbl_AlarmServiceAgreem
GROUP BY AlarmRef
) b ON a.ID = b.AlarmRef
ASKER
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!
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!