UPDATE with multiple records per updated record?

UPDATE with multiple records per updated record?

Post by Uri Diman » Mon, 26 Aug 2002 17:01:55



Mark, you must concatenate  letters  group by t1f2 and
use dynamic sql (it is might stored procedure that get parameter like
'abc' )

as




> FROM t2, t1
> WHERE t1f1 = t2f1'



Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.
Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)

 
 
 

UPDATE with multiple records per updated record?

Post by John Bel » Mon, 26 Aug 2002 19:49:58


Hi Mark

This will work and I can't think of a better way at the moment!

CREATE PROCEDURE prConcatenateF2 AS
BEGIN


DECLARE t2cursor INSENSITIVE CURSOR FOR
SELECT t2f1, t2f2 FROM t2 ORDER BY t2f1
OPEN t2cursor


BEGIN
 UPDATE t1



END
CLOSE t2cursor
DEALLOCATE t2cursor
END

I've assumed that the initialisation of t1f2 does not have the comma and you
don't want the trailing comma. You may want to exclude null t2f2 in the
cursor.

John


Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.
Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)


 
 
 

UPDATE with multiple records per updated record?

Post by DDS » Tue, 27 Aug 2002 02:14:44



Quote:> This will work and I can't think of a better way at the moment!

Let me help you.

This will allow u to easily create a concatenated delimited string
for any column(s) for any group by column(s) without using
cursors or UDF's.Then u can use the concatenated string in
the result to easily update a column in another table using
an Update statement.


www.rac4sql.net

 
 
 

UPDATE with multiple records per updated record?

Post by Mikhail Berlyan » Tue, 27 Aug 2002 15:13:01


Hey Mark.
Try this:

select col1, cast(col2 as varchar(50)) as col2 into #TempTable
from Table2 order by col1, col2


update #TempTable

else '' end + col2,

update Table1
set col2 = Table1.col2 + ', ' + a.col2
from #TempTable a
where a.col2 = (select max(#TempTable.col2) from #TempTable where
#TempTable.col1 = a.col1)
and a.col1 = Table1.col1
select * from Table1

drop table #TempTable

--

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
Brainbench MVP for Visual Basic   www.brainbench.com


Quote:> I haven't been able to figure this one out yet. Maybe someone can tell me

why this isn't working. I am trying to concatenate an updated field onto
itself (SET t1.t1f2 = t1.t1f2 + t2.t2f2 + ','). Judging by the results, only
the last record read where the first column in both tables matches is being
stored in the updated table.
Quote:

> Here's an example

> Table 2
> col1  col2
> ----------
> 1     a
> 1     b
> 1     c
> 2     r
> 2     s

> Table 1 before update
> t1
> col1  col2
> ----------
> 1     DEFAULT
> 2     DEFAULT

> After the update, table 1 should look like
> t1
> col1  col2
> ----------
> 1     DEFAULT,a,b,c,
> 2     DEFAULT,r,s,

> instead the update only produces
> t1
> col1  col2
> ----------
> 1     DEFAULT,c,
> 2     DEFAULT,s,

> Here's the code and output

> CREATE TABLE t1
> (
> t1f1 int,
> t1f2 varchar(50) DEFAULT 'DEFAULT,'
> )

> CREATE TABLE t2
> (
> t2f1 int,
> t2f2 varchar(10)
> )

> INSERT INTO t2
> (
> t2f1,
> t2f2
> )(
> SELECT 1, 'a'
> UNION
> SELECT 1, 'b'
> UNION
> SELECT 1, 'c'
> UNION
> SELECT 2, 'e'
> UNION
> SELECT 2, 'f'
> UNION
> SELECT 2, 'g'
> UNION
> SELECT 3, 'h'
> )

> INSERT INTO t1
> (
> t1f1
> )(
> SELECT DISTINCT
> t2f1
> FROM t2
> )

> UPDATE t1
> SET t1f2 = t1f2 + t2f2 + ','
> FROM t2, t1
> WHERE t1f1 = t2f1
> -- I have also tried full outer join, with the same result

> PRINT 'Contents of t1 after update'
> SELECT *
> FROM t1

> SELECT *
> FROM t2

> DROP TABLE t1
> DROP TABLE t2

> OUTPUT:
> --------

> (7 row(s) affected)

> (3 row(s) affected)

> (3 row(s) affected)

> Contents of t1 after update
> t1f1        t1f2
> ----------- --------------------------------------------------
> 1           DEFAULT,c,
> 2           DEFAULT,g,
> 3           DEFAULT,h,

> (3 row(s) affected)

> t2f1        t2f2
> ----------- ----------
> 1           a
> 1           b
> 1           c
> 2           e
> 2           f
> 2           g
> 3           h

> (7 row(s) affected)

 
 
 

1. Multiple SQL UPDATEs per record

I need to update a Fox Pro database from FileMaker each day.  Each Fox Pro dbf
record has 30 fields which need to be updated.

I am using FMP 5.5 and am using the Execute SQL Script step to query and update
the Fox Pro datafile.

I have used FMP for a long time but never used SQL before.  I have managed from
documents I've downloaded from the Web learn how to write a simple UPDATE
command (that works).

However, I can only figure out how to update one field per query.  While I
could create multiple SQL statements for each field (and run through each of
them per record) the reality is that the Fox Pro datafile has 30 fields and
about 200,000 records.  Executing a query for every field over that many
records will bog down the system.

Does anyone know how to create an SQL UPDATE statement that will update
multiple fields per query?  The statement I current have is:

UPDATE Member.dbf SET USERNAME='Name' WHERE RECORDNUM='recnum';

I want something like:

UPDATE Member.dbf SET USERNAME='Name' SET PASSWORD='pwrd' SET ADDRESS='address'
SET CITY='cty' SET ZIP='zipcode' WHERE RECORDNUM='recnum';

I've looked on the Web and see other frustrated users looking to do a similar
update but I can't find an answer on how to resolve from FileMaker.

Thanks!

2. mailing list

3. Import multiple records per record?

4. access sqlserver with a single DLL over TCP/IP

5. Cached Updates QUESTION: Sorting table of updated and non-updated records

6. access rights for records

7. Update on Multiple Records from Multiple Tables

8. SQL Server SO slow...

9. multiple record update

10. Insert,Update,Delete Record in Multiple Table

11. Insert,Update,Delete Record in Multiple Table-URGENT

12. Update multiple records with REPLACE

13. Triggers - Multiple records updated