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