July 10, 2006 at 1:05 pm
There are 2 tables: tblA: id (int), name, email, age (all char) and tblB: id (int), columnname and value (all char). I need to update tblA columns set them to tblB.value where A.id=B.id and B.columnname=A.name (or age or email). For example for name column, update tblA set name=b.value from tblA a inner join tblB b on a.id=b.id and b.columnname=’name’ (same for age, email). As I have 14 columns to update in tblA how can I do this in more elegant way than I just mentioned? Thanks
July 10, 2006 at 1:40 pm
I'm having a little trouble understanding your question... While I rarely advocate using cursors, this seems like the ideal time to make use of one.
DECLARE @sql NVARCHAR ( 4000 )
, @column_nm NVARCHAR ( 128 )
DECLARE cur CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_SCHEMA = 'tblA'
AND COLUMN_NAME 'id' -- Don't want to update your key.
OPEN cur
FETCH NEXT FROM cur INTO @column_nm
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'UPDATE a
SET a.[' + @column_nm + '] = b.value
FROM tblA a
JOIN tblB b
ON a.id = b.id
AND b.columnname = ''' + @column_nm + ''' '
EXEC sp_executesql @sql
FETCH NEXT FROM cur INTO @column_nm
END
CLOSE cur
DEALLOCATE cur
July 10, 2006 at 1:46 pm
Ack... change one of those lines:
WHERE TABLE_SCHEMA = 'tblA'
should read:
WHERE TABLE_NAME = 'tblA'
July 10, 2006 at 1:52 pm
Aaron, thanks. I've already done with cursor and dynamic sql (almost your variant). Actually I try to avoid using cursors so I hoped for another decision (what I called "more elegant"). Till I find something else I'll use cursor version. Thanks again.
July 10, 2006 at 2:04 pm
You could also do something like this, however I don't know if I'd call that more "elegant":
UPDATEa
SETa.name =
CASE WHEN b.columnname = 'name' THEN b.value
ELSE a.name
,a.email =
CASE WHEN b.columnname = 'email' then b.value
ELSE a.email
,a.age =
CASE WHEN b.columnname = 'age' then b.value
ELSE a.age
...
FROMtblA a
JOINtblB b
ON a.id = b.id
July 10, 2006 at 2:05 pm
Ack... I'm off today. After each of those ELSE statements there needs to be an END.
July 10, 2006 at 2:11 pm
From my busines point of view cursor variant is better because in case new columns will be added this variant will manage them automatically. Thanks
July 10, 2006 at 4:52 pm
UPDATE A
SET name = ISNULL(BN.value, A.name),
email = ISNULL(be.Value, A.email),
age = ISNUL(BA.value, A.age)
FROM tblA A
LEFT JOIN tblB BN ON BN.id = A.id and BN.columnname = 'name'
LEFT JOIN tblB BE ON BE.id = A.id and BE.columnname = 'email'
LEFT JOIN tblB BA ON BA.id = A.id and BA.columnname = 'age'
Add missing 11 joins - and polnyj vperiod!
_____________
Code for TallyGenerator
July 10, 2006 at 9:06 pm
Sergey, thanks. I need to evaluate what is better for me- use your approach but maintain code manually (in case new fields will be added I need to add same number of extra left joins) or use cursor and code will be managed automatically. Thanks again and good luck!
July 10, 2006 at 9:42 pm
If you change the schema (set of returned fields is a part of schema) you need to change the code.
But it must not be manual.
If you have a table with list of fields to be returned you may set ua a trigger on this table having dynamic script to ALTER you SP according to the new set of fields.
_____________
Code for TallyGenerator
July 11, 2006 at 7:20 am
OK, thanks, I hope I've got it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply