July 24, 2014 at 1:19 am
create table #tmpcopylibrary
(
dataid int,
value int
)
insert #tmpcopylibrary select 1,999
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = TableA.dataid), 0)
here tableA has the dataid 1 as we have in temp table (tmpcopylibrary), still the value is not upldated in TableA.
Can anyone help me to know the reason or any idea?
July 24, 2014 at 2:11 am
Check how many rows match:
SELECT a.dataid, x.Value
FROM TableA a
CROSS APPLY (
SELECT TOP 1 t.Value
FROM #tmpcopylibrary t
WHERE t.dataid = a.dataid
ORDER BY (SELECT NULL)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 24, 2014 at 2:24 am
dkriplani97 (7/24/2014)
create table #tmpcopylibrary(
dataid int,
value int
)
insert #tmpcopylibrary select 1,999
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = TableA.dataid), 0)
here tableA has the dataid 1 as we have in temp table (tmpcopylibrary), still the value is not upldated in TableA.
Can anyone help me to know the reason or any idea?
The code "does some work" where there is a matching dataid for the result of the subquery, but sets all other values to 0.
😎
USE tempdb;
GO
CREATE TABLE dbo.TableA
(
dataid int
,value int
)
INSERT INTO dbo.TableA(dataid,value) VALUES(1,1),(2,2),(3,3);
create table #tmpcopylibrary
(
dataid int,
value int
)
insert #tmpcopylibrary select 1,999;
SELECT * FROM dbo.TableA
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = TableA.dataid), 0)
SELECT * FROM dbo.TableA
DROP TABLE #tmpcopylibrary
DROP TABLE dbo.TableA
Results
Before
dataid value
----------- -----------
1 1
2 2
3 3
After
dataid value
----------- -----------
1 999
2 0
3 0
Somehow I believe that this isn't what you are after.
July 24, 2014 at 2:36 am
Hi Eric,
it is setting all value to zero on a setup, where the temp table having matching dataid
August 1, 2014 at 8:52 am
this update statement is working
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = tempquerydataid), 0).
however this is not working
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = TableA.tempquerydataid), 0)
any help for this.
August 1, 2014 at 8:59 am
Use the one which is working.
If this answer is insufficient, please make your question more clear. What are you trying to do? What are you trying to ask?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2014 at 11:11 am
dkriplani97 (8/1/2014)
this update statement is workingUPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = tempquerydataid), 0).
however this is not working
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary
where dataid = TableA.tempquerydataid), 0)
any help for this.
The table definitions you posted earlier for these tables don't show any column named "tempquerydataid". I'm guessing from this code that the temp table has a column named tempquerydataid, but not TableA.
When using multiple tables in queries, you should always qualify all columns, for example:
UPDATE TableA
SET Value = isnull((select top 1 Value from #tmpcopylibrary tcl
where TableA.dataid = tcl.tempquerydataid), 0)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply