SQL Query problem concat text field from mulitple rows into one text field

SQL Query problem concat text field from mulitple rows into one text field

Post by Ashok Pate » Sun, 26 Aug 2001 02:12:00



Does anybody know how to update a single text field in a
table from multiple rows in another table.

Thanks

 
 
 

SQL Query problem concat text field from mulitple rows into one text field

Post by Robert Elli » Sun, 26 Aug 2001 02:47:10


If you mean "from multiple columns in another table" , sure,

Update TableX
    SET FieldSingleText= TableY.Field1+TableY.Field2
    FROM TableY
    Where TableX.PrimaryKeyField=TableY.PrimaryKeyField

NB If you're using mssql, issue
set concat_null_yields_null off
before you run it ,

hth
R.


Quote:> Does anybody know how to update a single text field in a
> table from multiple rows in another table.

> Thanks


 
 
 

SQL Query problem concat text field from mulitple rows into one text field

Post by Ashok Pate » Sun, 26 Aug 2001 03:11:47


Thanks Rob,
However that is not my problem;
Here it is.
Table A;
ID   Desc
1    NULL
2    NULL
3    NULL

Table B;
ID   CODE
1    AS
1    SN
1    PT
2    SN
2    RN
3    AM
3    SN
3    RN
3    PT

I need table A updates to result in
ID   Desc
1    AS SN PT
2    SN RN
3    AM SN RN PT

Do you my problem now.... Any ideas you may have would be very helpful...

Thanks
AP

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

SQL Query problem concat text field from mulitple rows into one text field

Post by Robert Elli » Sun, 26 Aug 2001 03:26:17


There must be an elegant way of doing it--but I don't know what it is ;
This code won't run of course, just gives you an idea....

set concat_null_yields...

update tableA set desc=null


create cursor UA local forward_only read_only
for
select id, code from Table B
order by /* if it matters.... */

open UA



    begin

    fetch next ...
    end

close UA
deallocate ua

set concat ON
---

hth
Robert.

Update

Quote:

> Thanks Rob,
> However that is not my problem;
> Here it is.
> Table A;
> ID   Desc
> 1    NULL
> 2    NULL
> 3    NULL

> Table B;
> ID   CODE
> 1    AS
> 1    SN
> 1    PT
> 2    SN
> 2    RN
> 3    AM
> 3    SN
> 3    RN
> 3    PT

> I need table A updates to result in
> ID   Desc
> 1    AS SN PT
> 2    SN RN
> 3    AM SN RN PT

> Do you my problem now.... Any ideas you may have would be very helpful...

> Thanks
> AP

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

SQL Query problem concat text field from mulitple rows into one text field

Post by Ashok Pate » Sun, 26 Aug 2001 04:04:15


This is great...Rob Thanks again...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Problem with updating from TEXT field into TEXT field

Hi, I have this query

UPDATE wkt2686
SET extra_text = memo
FROM payment_test p
WHERE wkt2686.payment_pointer = p.payment_pointer
AND type = 'E'
AND wkt2686.payment_pointer != 0

Where columns extra_text and memo are both TEXT datatypes.

This script crashes with Database connection failure DBProcess dead.  Howver
if I amend the script as follows it works fine.  Any ideas ?

UPDATE wkt2686
SET extra_text = CONVERT (TEXT,memo)
FROM payment_test p
WHERE wkt2686.payment_pointer = p.payment_pointer
AND type = 'E'
AND wkt2686.payment_pointer != 0

The Server is Intel Pentium NT4 SP3 and SQL 6.5 SP4

Regards

Jeff Tulley
MATRIX

2. Converted forms 30 to 45 problem

3. Concat mulitple rows from a table problem.

4. 4GL 4.14 and Reports...

5. Copy text field from one row to another???

6. CDML - help searhing a date range using pulldowns on the web?

7. Text Field -> Repeating Calculation (Text) Field

8. Help....I need to add a field to a table with 1.9 m records.

9. Webpage won't show text field but will show calc of text field

10. extracting address text from one field into separate fields

11. Paradox merging two text fields into one one

12. Text justification problems with 4.1 on Windows (fields and text boxes)

13. PROBLEMS INSERTING MEMO FIELDS FROM TABLE FIELD TO TEXT FILE