Problem with an update

Hi, I've a table "Table1" with:
ID - Cod - Type - Value - Period
1-COD1-AAA-0-Jan
2-COD2-BBB-0-Feb
3-COD3-AAA-0-Feb
4-COD4-CCC-0-Feb
Now I want to UPDATE this records using a Second Table "Table2"
Type-Qt-Value-Period
AAA-10-10-Jan
AAA-3-2-Feb
BBB-3-2-Feb
CCC-4-6-Feb
...
If, in table1, I've AAA I want to search in table2 the value of type AAA in
the table1.period
If, in table1, I've BBB I want to search in table2 the value of type BBB in
the table1.period
How Can I create this update'
After the UPDATE, I would like to obtain:
ID - Cod - Type - Value - Period
1-COD1-AAA-100-Jan (value = 10*10)
2-COD2-BBB-6-Feb (value = 3*2)
3-COD3-AAA-6-Feb (value = 3*2)
4-COD4-CCC-24-Feb (value = 6*4)
ThanksIdentity wrote:
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA i
n
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB i
n
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks
It really helps if you post DDL and INSERT statements instead of
sketches of tables - it could even save you some typing. Looking at the
information given it appears that the UPDATE is to be based on joining
the tables on type and period, but you've left us to guess whether
those two columns are unique in either table. If the combination of
those two columns isn't unique in Table2 then do you want to SUM the
multiple rows? If they aren't unique in Table1 then do you want the
same value figure to appear on multiple rows? If they are unique in
BOTH tables then is there a particular reason for having two tables
instead of one?
Notwithstanding the above uncertainties, here's my untested guess at
what you want:
UPDATE Table1
SET value =
(SELECT SUM(qt*value)
FROM Table2
WHERE type = Table1.type
AND period = Table1.period);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here's one method:
CREATE TABLE dbo.FirstTable
(
ID int NOT NULL
CONSTRAINT PK_FirstTable PRIMARY KEY,
Cod char(4) NOT NULL,
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Period char(3) NOT NULL
)
INSERT INTO dbo.FirstTable
SELECT 1,'COD1','AAA',0,'Jan'
UNION ALL SELECT 2,'COD2','BBB',0,'Feb'
UNION ALL SELECT 3,'COD3','AAA',0,'Feb'
UNION ALL SELECT 4,'COD4','CCC',0,'Feb'
CREATE TABLE dbo.SecondTable
(
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Qt int NOT NULL,
Period char(3) NOT NULL,
CONSTRAINT PK_SecondTable
PRIMARY KEY ([Type], Period)
)
INSERT INTO dbo.SecondTable
SELECT 'AAA',10,10,'Jan'
UNION ALL SELECT 'AAA',3,2,'Feb'
UNION ALL SELECT 'BBB',3,2,'Feb'
UNION ALL SELECT 'CCC',4,6,'Feb'
UPDATE dbo.FirstTable
SET
Value =
(SELECT [Value]*Qt
FROM dbo.SecondTable
WHERE
SecondTable.[Type] = FirstTable.[Type] AND
SecondTable.[Period] = FirstTable.[Period]
)
SELECT
ID,
Cod,
[Type],
[Value],
Period
FROM dbo.FirstTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Identity" <id@.id.it> wrote in message
news:uQxOzbpoGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA
> in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB
> in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks|||Thanks for help!
But If I've many fields I must to use

> value =
> (SELECT SUM(qt*value)
> FROM Table2
> WHERE type = Table1.type
> AND period = Table1.period);
for all fields
Thankssql

Problem with an update

Hi, I've a table "Table1" with:
ID - Cod - Type - Value - Period
1-COD1-AAA-0-Jan
2-COD2-BBB-0-Feb
3-COD3-AAA-0-Feb
4-COD4-CCC-0-Feb
Now I want to UPDATE this records using a Second Table "Table2"
Type-Qt-Value-Period
AAA-10-10-Jan
AAA-3-2-Feb
BBB-3-2-Feb
CCC-4-6-Feb
...
If, in table1, I've AAA I want to search in table2 the value of type AAA in
the table1.period
If, in table1, I've BBB I want to search in table2 the value of type BBB in
the table1.period
How Can I create this update'
After the UPDATE, I would like to obtain:
ID - Cod - Type - Value - Period
1-COD1-AAA-100-Jan (value = 10*10)
2-COD2-BBB-6-Feb (value = 3*2)
3-COD3-AAA-6-Feb (value = 3*2)
4-COD4-CCC-24-Feb (value = 6*4)
ThanksIdentity wrote:
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks
It really helps if you post DDL and INSERT statements instead of
sketches of tables - it could even save you some typing. Looking at the
information given it appears that the UPDATE is to be based on joining
the tables on type and period, but you've left us to guess whether
those two columns are unique in either table. If the combination of
those two columns isn't unique in Table2 then do you want to SUM the
multiple rows? If they aren't unique in Table1 then do you want the
same value figure to appear on multiple rows? If they are unique in
BOTH tables then is there a particular reason for having two tables
instead of one?
Notwithstanding the above uncertainties, here's my untested guess at
what you want:
UPDATE Table1
SET value = (SELECT SUM(qt*value)
FROM Table2
WHERE type = Table1.type
AND period = Table1.period);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here's one method:
CREATE TABLE dbo.FirstTable
(
ID int NOT NULL
CONSTRAINT PK_FirstTable PRIMARY KEY,
Cod char(4) NOT NULL,
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Period char(3) NOT NULL
)
INSERT INTO dbo.FirstTable
SELECT 1,'COD1','AAA',0,'Jan'
UNION ALL SELECT 2,'COD2','BBB',0,'Feb'
UNION ALL SELECT 3,'COD3','AAA',0,'Feb'
UNION ALL SELECT 4,'COD4','CCC',0,'Feb'
CREATE TABLE dbo.SecondTable
(
[Type] char(3) NOT NULL,
[Value] int NOT NULL,
Qt int NOT NULL,
Period char(3) NOT NULL,
CONSTRAINT PK_SecondTable
PRIMARY KEY ([Type], Period)
)
INSERT INTO dbo.SecondTable
SELECT 'AAA',10,10,'Jan'
UNION ALL SELECT 'AAA',3,2,'Feb'
UNION ALL SELECT 'BBB',3,2,'Feb'
UNION ALL SELECT 'CCC',4,6,'Feb'
UPDATE dbo.FirstTable
SET
Value = (SELECT [Value]*Qt
FROM dbo.SecondTable
WHERE
SecondTable.[Type] = FirstTable.[Type] AND
SecondTable.[Period] = FirstTable.[Period]
)
SELECT
ID,
Cod,
[Type],
[Value],
Period
FROM dbo.FirstTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Identity" <id@.id.it> wrote in message
news:uQxOzbpoGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi, I've a table "Table1" with:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-0-Jan
> 2-COD2-BBB-0-Feb
> 3-COD3-AAA-0-Feb
> 4-COD4-CCC-0-Feb
> Now I want to UPDATE this records using a Second Table "Table2"
> Type-Qt-Value-Period
> AAA-10-10-Jan
> AAA-3-2-Feb
> BBB-3-2-Feb
> CCC-4-6-Feb
> ...
> If, in table1, I've AAA I want to search in table2 the value of type AAA
> in
> the table1.period
> If, in table1, I've BBB I want to search in table2 the value of type BBB
> in
> the table1.period
>
> How Can I create this update'
> After the UPDATE, I would like to obtain:
> ID - Cod - Type - Value - Period
> 1-COD1-AAA-100-Jan (value = 10*10)
> 2-COD2-BBB-6-Feb (value = 3*2)
> 3-COD3-AAA-6-Feb (value = 3*2)
> 4-COD4-CCC-24-Feb (value = 6*4)
> Thanks|||Thanks for help!
But If I've many fields I must to use
> value => (SELECT SUM(qt*value)
> FROM Table2
> WHERE type = Table1.type
> AND period = Table1.period);
for all fields
Thanks

Problem when editing existing report

Hi,
I have created Report1.rdl, using a simple select such as
SELECT * FROM Table1 .
Now I want to filter the result, so I go to the Data view, and add a filter
into the GroupCode field.
But when I run again the report, it doesn´t show any data, only the header.
Isn´t it possible to edit a report once you have created it? I suppose it is
possible so, what am I doing wrong?
Thanks in advance,
Ibai PeñaI have found why it happens. I compare the filter field with a blank value,
and no record is found.
Now my question is: Is it possible to create a filter, and make be able to
use it or not, depending on what the user wants?
I have succed filtering data, but once filtered, I´m not able to see all
data again.
Thanks in advance,
Ibai Peña
"Ibai Peña" wrote:
> Hi,
> I have created Report1.rdl, using a simple select such as
> SELECT * FROM Table1 .
> Now I want to filter the result, so I go to the Data view, and add a filter
> into the GroupCode field.
> But when I run again the report, it doesn´t show any data, only the header.
> Isn´t it possible to edit a report once you have created it? I suppose it is
> possible so, what am I doing wrong?
> Thanks in advance,
> Ibai Peña|||My guess is that the filter applied does not return any data and so it is
blank.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ibai Peña" <IbaiPea@.discussions.microsoft.com> wrote in message
news:A71B86D7-560C-4C3F-90C2-11D0947EFBF5@.microsoft.com...
> Hi,
> I have created Report1.rdl, using a simple select such as
> SELECT * FROM Table1 .
> Now I want to filter the result, so I go to the Data view, and add a
filter
> into the GroupCode field.
> But when I run again the report, it doesn´t show any data, only the
header.
> Isn´t it possible to edit a report once you have created it? I suppose it
is
> possible so, what am I doing wrong?
> Thanks in advance,
> Ibai Peña

Problem updating table1 with data from table 2

Hi,

I'm trying to loop through 2 tables and match the records from table1 to table2 and update a couple of columns in table2 if there is a match. The below code updates everyone in table2 with the same data from only 1 record. How can I keep looping through both tables?

USE GradData

GO

Declare @.ssn nvarchar(10)

Declare @.testdesc nvarchar(10)

Declare @.testcode nvarchar(10)

Declare @.testdate datetime

Declare @.testscore nvarchar(50)

Declare @.testsource nvarchar(10)

Declare @.testsortkey nvarchar(10)

Declare @.fcode nvarchar(10)

Declare @.ssn2 nvarchar(10)

Declare @.testdesc2 nvarchar(10)

Declare @.testcode2 nvarchar(10)

Declare @.testdate2 datetime

Declare @.testscore2 nvarchar(50)

Declare @.testsource2 nvarchar(10)

Declare @.testsortkey2 nvarchar(10)

Declare @.fcode2 nvarchar(10)

Declare mycursor Cursor For

Select ssn,testDesc1,testCode1,testDate1,testScore1,testSource1,Fcode1,sortkey

From TestScoresRpt

Open mycursor

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

Declare mycursor2 Cursor For

Select ssn,test_Desc,test_Code,test_Date,test_Score,test_Source_code,Fcode,test_sort_key

From ScoresAll

Open mycursor2

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.ssn <> @.ssn2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey = @.testsortkey2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2

Update TestScoresRpt

set ssn2=@.ssn2,testDesc2=@.testDesc2,testCode2=@.testCode2,testDate2=@.testDate2,testScore2=@.testScore2,testSource2=@.testSource2,Fcode2=@.fcode2,sortkey2=@.testsortkey2

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

END

CLOSE mycursor

CLOSE mycursor2

DEALLOCATE mycursor

DEALLOCATE mycursor2

If you are updating all rows where (@.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2), then you would have improved performance using a Set based operation.

Please verify that the end result is two (2) virtually duplicate rows.

|||

THE END RESULT WILL BE A SSN WITH 2 DIFFERENT SORT KEYS FOR EACH SSN IN THE TABLE. WHAT DO YOU MEAN BY SET BASED OPERATION?

THANKS,

|||

My mistake for not completely reading your code.

A Set based operation would be making these updates in one query.

Try something like:

UPDATE t
SET
t.ssn2 = s.ssn,
t.testDesc2=s.testDesc2,
t.testCode2=s.testCode2,
t.testDate2=s.testDate2,
t.testScore2=s.testScore2,
t.testSource2=s.testSource2,
t.Fcode2=s.fcode2,
t.sortkey2=s.testsortkey2
FROM TestScoresRpt t
JOIN ScoresAll s
ON t.ssn = s.ssn
WHERE t.testsortkey <> s.testsortkey2

(This is untested, so please test, test.)