HI,
I want to know if is possible to update Table1.Col5 from an
Table2.Col3 ??
The Problem is Col1 and Col2 doesn't have a same size.
Exemple of record.

Table 1
Col1 Col2 Col3 Col4 Col5
10 4001 F1 1 NULL
10 4001 F1 1 NULL
10 4003 F5 3 NULL


Table 2
Col1 Col2 Col3
10 4001 S
10 4001 P
10 5009 S
24 4001 P

I tried to update T1.Col5 with T2.Col3 by i got this result
I used INNER join Table1 and Table2 on Col1 and Col2.

Table 1
Col1 Col2 Col3 Col4 Col5
10 4001 F1 1 S
10 4001 F1 1 S
10 4003 F5 3 NULL

Can you help me please.
Thanks in advance.

"SAM" <saberb (AT) hotmail (DOT) com> wrote

I don't understand what end result you want. Please could you post your
required results together with some explanation. I'd guess:

UPDATE table1
SET col5 =
(SELECT col3
FROM table2
WHERE table2.col1 = table1.col1
AND table2.col2 = table2.col2);

--
David Portas

On Feb 15, 3:30*pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote:
HI,
I got this result
Table 1
but
my required results is

Table 1
Col1 Col2 Col3 Col4 Col5
10 4001 F1 1 S
10 4001 F1 1 P<--------
10 4003 F5 3 NULL
i want a 'P' en the second record not a 'S'


thanks for your help

SAM (saberb (AT) hotmail (DOT) com) writes:
I think you need to explain not only what you want, but also why you want
it. Why would the result be S in one case and P in the other? What does
the columns represent?

The best approach to get good answers for such questions is to post:

o CREATE TABLE statments for your tables. Don't forget to indicate keys.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative describing your problem.

With the first three it's to copy and paste into a query editor to
develop a tested query. Also having tables, sample data and narrative
helps to understand the problem.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"SAM" <saberb (AT) hotmail (DOT) com> wrote

Please explain how you arrive at that result. Also, what are the keys of the
two tables? Every table should have a key but Table1 has none that I can
see. As Erland says, the easiest way to describe your problem is to post a
CREATE TABLE (with keys please) and some INSERTs.

--
David Portas

On 16 fév, 05:15, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote:
Hi, there is more explanation: I know is too long, but i post-it
because i have a hard time with this probleme.

Thanks again for your help.

CREATE TABLES
--------------------------
Create Table Report (

Province Varchar (2) NOT NULL,
Asset Varchar (4) NOT NULL,
AssetClass Varchar(75) NOT NULL,
Component Numeric NOT NULL,
QuantF1 varchar(1) NULL,
QuantF2 varchar(1) NULL,
QuantF3 varchar(1) NULL,
QuantF4 varchar(1) NULL,
QuantF5 varchar(1) NULL

)

No PK in this Table I use-it to product a report.

Records:
----------
Province Asset AssetClass Component QuantF1 QuantF2 QuantF3 QuantF4
QuantF5
10 4001 indistry 1 Null
Null Null Null Null
10 4002 commercial 1 Null
Null Null Null Null
10 4002 commercial 1 Null
Null Null Null Null
10 4090 Transport 1 Null
Null Null Null Null
10 5008 Engieering 3 Null
Null Null Null Null

11 4001 indistry 1 Null
Null Null Null Null
11 4002 commercial 1 Null
Null Null Null Null
11 4002 commercial 1 Null
Null Null Null Null
11 4090 Transport 1 Null
Null Null Null Null
11 4090 Transport 1 Null
Null Null Null Null
11 5008 Engieering 3 Null
Null Null Null Null

12 4001 indistry 1 Null
Null Null Null Null
12 4001 indistry 1 Null
Null Null Null Null
12 4002 commercial 1 Null
Null Null Null Null
12 4002 commercial 1 Null
Null Null Null Null
12 4090 Transport 1 Null
Null Null Null Null
12 4090 Transport 1 Null
Null Null Null Null
12 5008 Engieering 3 Null
Null Null Null Null
12 5008 Engieering 3 Null
Null Null Null Null

...............
.
.
.
.
.
.
.
.
.
.
.
.

Create TableQuantF (

Code Varchar (5) NOT NULL,
Province Varchar (2) NOT NULL,
Asset Varchar (4) NOT NULL,
QuantF varchar (1) NOT NULL

)

For this Table PK (CODE, Province, Asset, QuantF)

Records :
-----------


CODE Province Asset QuantF
F1 10 4001 P
F1 10 4002 P
F1 10 4002 S
F1 10 4090 P
F1 10 4090 S
F1 10 5008 P
F1 11 4001 P
F1 11 4002 P
F1 11 4002 S
F1 11 4090 P
F1 11 4090 S
F1 12 4001 P
F1 12 4001 S
F1 12 4002 P
F1 12 4002 S
F1 12 4090 P
F1 12 4090 S
F1 12 5008 P

F2 10 4001 P
F2 10 4002 P
F2 10 4002 S
F2 10 4090 P
F2 10 4090 S
F2 10 5008 P
F2 10 5008 S
F2 11 4001 P
F2 11 4001 P
F2 11 4002 P
F2 11 4002 S
F2 11 4090 P
F2 11 4090 S
F2 11 5008 P
F2 12 4001 P
F2 12 4002 P
F2 12 4002 S
F2 12 4090 P
F2 12 4090 S
F2 12 5008 P
F2 12 5008 S
..........

What I want to do is to update my Report Table with TableQuantF

Query :
--------------
Insert Into tblTemp
Select Province , Asset, QuantF From TableQuantF
Where CODE = 'F1'

TblTemp:
------------
Province , Asset, QuantF,
10 4001 P
10 4002 P
10 4002 S
10 4090 P
10 4090 S
10 5008 P
11 4001 P
11 4002 P
11 4002 S
11 4090 P
11 4090 S
12 4001 P
12 4001 S
12 4002 P
12 4002 S
12 4090 P
12 4090 S
12 5008 P

Update TableQuantF
Set TableQuantF.QuantF1 = tblTemp.QuantF
Where TableQuantF.Province = tblTemp.Province
And
TableQuantF.Asset= tblTemp.Asset

The update I want to do is QuanF1 and after that QuantF2 .....QuantF5
I can have only two Value for QuantF (P and S) By Asset.

Result update for QuantF1:
-------------------------
Province Asset AssetClass Component QuantF1 QuantF2 QuantF3 QuantF4
QuantF5
10 4001 indistry 1 P
Null Null Null Null
10 4002 commercial 1 P
Null Null Null Null
10 4002 commercial 1 P
Null Null Null Null
10 4090 Transport 1 P
Null Null Null Null
10 4090 Transport 1 P
Null Null Null Null
10 5008 Engieering 3 P
Null Null Null Null

11 4001 indistry 1 P
Null Null Null Null
11 4002 commercial 1 P
Null Null Null Null
11 4002 commercial 1 P
Null Null Null Null
11 4090 Transport 1 P
Null Null Null Null
11 4090 Transport 1 P
Null Null Null Null
11 5008 Engieering 3 Null
Null Null Null Null

12 4001 indistry 1 P
Null Null Null Null
12 4001 indistry 1 P
Null Null Null Null
12 4002 commercial 1 P
Null Null Null Null
12 4002 commercial 1 P
Null Null Null Null
12 4090 Transport 1 P
Null Null Null Null
12 4090 Transport 1 P
Null Null Null Null
12 5008 Engieering 3 P
Null Null Null Null
12 5008 Engieering 3 P
Null Null Null Null

The problem:

Every time I update QuantF1 column I get this result for a double
(Province Asset ...component)

Province Asset AssetClass Component QuantF1 QuantF2 QuantF3 QuantF4
QuantF5
10 4002 commercial 1 P
Null Null Null Null
10 4002 commercial 1 P
Null Null Null Null
The result I want is this
Province Asset AssetClass Component QuantF1 QuantF2 QuantF3 QuantF4
QuantF5
10 4002 commercial 1 P
Null Null Null Null
10 4002 commercial 1 S
Null Null Null Null
Not P in the second Record I Want S.
Thanks in advance. If you need more explanation please tell me.

SAM (saberb (AT) hotmail (DOT) com) writes:
And I am sorry, but I have a hard time to understand you. First of all,
I asked to supply the sample data as *INSERT statements*. I didn't say
only to be evil. If you provide the data as INSERT statements, we can
easily copy and paste it into a query editor to work with it. That is,
use it as test data for the queries we develop, but also to analyse the
data in more detail.

Yes, with some editing skills, I could easy convert your tables into
INSERT statements, but if you are not preprared to make that effort
yourself, why should I be?

Next, there are several inconsistencies in your post, which makes it
difficult to understand what you are looking for.

But below there is no table Report. There is one called TblTemp,
which may be the same table, but I don't know.

OK.

OK. Except that if TblTemp is another name for the table Report, this
INSERT statement cannot work because the table definition does not
match the SELECT statement.

But here you update TableQuantF? Which table do you really intend to
update?

I guess that the last update is wrong, but I really don't want to
work from guesses. And if it's TblTemp you want to update, I don't
see the point if you just insert QuantF?

And assuming that you want to populate QuantF1 to QuantF5, why not tall
us how to populate them? I have a feeling that what you need is a crosstab,
but I don't know what you are really looking for.

I repeat what I said in my previous post, but I spell it our more
elaborated. Produce a script that:

1) Creates a database.
2) USE thatdb
3) Creates the tables.
4) Inserts the data in TableQuantF
5) Adds the data to the Report/TblTemp table.
6) Add the UPDATE statement you have.
7) In a different table, include the result you want - when all QuantF1
to F5 are populated.

And before you post *test* this script, so that you know that it runs.

Also, explain *why* you want the result you are looking for. That is,
what is the business problem?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx