November 2, 2012 at 9:01 am
HI,
We a have 2 tables.I need to update ds_size and create_dt columns values on DAT_DES table by comparing the Data_Des and DAT_STAGE tables coulumns.
update DAT_DES set
DAT_DES.ds_size = (select ds_size from DAT_STAGE where
DAT_DES.lvl_nme = LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) AND
DAT_DES.ds_nme = DAT_STAGE.ds_nme),
DAT_DES.create_dt = (select create_dt from DAT_STAGE where
DAT_DES.lvl_nme = LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) AND
DAT_DES.ds_nme = DAT_STAGE.ds_nme)
where str(DAT_DES.lvl_nme) + DAT_DES.ds_nme IN
(select LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) + ds_nme from DAT_STAGE)
I have return the above query but it is not working correctly.
Can any one help out here to wright the query in other method or in other way........
or to modify the above query...
Many thanks,
November 2, 2012 at 9:19 am
bala2 (11/2/2012)
HI,We a have 2 tables.I need to update ds_size and create_dt columns values on DAT_DES table by comparing the Data_Des and DAT_STAGE tables coulumns.
update DAT_DES set
DAT_DES.ds_size = (select ds_size from DAT_STAGE where
DAT_DES.lvl_nme = LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) AND
DAT_DES.ds_nme = DAT_STAGE.ds_nme),
DAT_DES.create_dt = (select create_dt from DAT_STAGE where
DAT_DES.lvl_nme = LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) AND
DAT_DES.ds_nme = DAT_STAGE.ds_nme)
where str(DAT_DES.lvl_nme) + DAT_DES.ds_nme IN
(select LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) + ds_nme from DAT_STAGE)
I have return the above query but it is not working correctly.
Can any one help out here to wright the query in other method or in other way........
or to modify the above query...
Many thanks,
With no ddl or details about what you are trying to do I came up with this. Not 100% sure it exactly what you are looking for but I think it it close.
UPDATE DAT_DES
SET dd.ds_size = ds.ds_size,
dd.create_dt = ds.create_dt
FROM DAT_STAGE ds
join DATE_DES dd on dd.lvl_nme = LTRIM(REPLACE(ds.lvl_nme, 'D', ''))
AND dd.ds_nme = ds.ds_nme
WHERE str(DAT_DES.lvl_nme) + DAT_DES.ds_nme IN (
SELECT LTRIM(REPLACE(DAT_STAGE.lvl_nme, 'D', '')) + ds_nme
FROM DAT_STAGE
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 2, 2012 at 9:28 am
Quick hint, write a select to find the rows you want to update first. Make sure that it returns the data you need.
November 5, 2012 at 2:56 am
CELKO (11/2/2012)
... This is a job for a MERGE INTO statement. Here is a guess:UPDATE Something_Destination AS D
USING Something_Staging AS S
...
You wanted to use MERGE, didn't you:
MERGE Something_Destination AS D
USING Something_Staging AS S
...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply