Hi,
I have the needs to  UPDATE only the first row with a SQL statement;

UPDATE table1 set flagx = 1 where Key in (select key from table2 where flagy
= 2);

Example data in table2:

Key   flagy
1         2
1
1         2
2         2
2
2         2
3
4
5

Example data in table1

key   flagx
1
1
1
1
1
2
2
2
2
3
3
3
3

Now, only the first 2 rows in table1 with key = 1 must have flagx with value
1; I need to update only the first row (with the key =1)  that don't have
value in flagx must be updated; So, with Update statement I need to  obtain:

table1
key     flagx
1          1
1          1
1
1
1
2         1
2         1
2
2
3
3
3
3

I have a record ID, naturally, in the table1, but I don't have an ID related
field in the table2 (an external file).

With the procedural way this is easy, but with SQL?
Is possible this ? How?

Any hint is appreciated,


Thanks in advance,

Stefano Monterisi

Sorry, but had trouble understanding your problem here. Do you want to
update one row for each key that has a match in table2? Or for all the
matches with table2?

Your example does not quite agree with your description.

Robert

"Stefano Monterisi" <info@sesamoweb.it> wrote in message
news:EA4014F5-959F-4972-92D6-4C141E8207BC@news.elevatesoft.com...
>
> Hi,
> I have the needs to  UPDATE only the first row with a SQL statement;
>
> UPDATE table1 set flagx = 1 where Key in (select key from table2 where
> flagy = 2);
>
> Example data in table2:
>
> Key   flagy
> 1         2
> 1
> 1         2
> 2         2
> 2
> 2         2
> 3
> 4
> 5
>
> Example data in table1
>
> key   flagx
> 1
> 1
> 1
> 1
> 1
> 2
> 2
> 2
> 2
> 3
> 3
> 3
> 3
>
> Now, only the first 2 rows in table1 with key = 1 must have flagx with
> value 1; I need to update only the first row (with the key =1)  that don't
> have value in flagx must be updated; So, with Update statement I need to
> obtain:
>
> table1
> key     flagx
> 1          1
> 1          1
> 1
> 1
> 1
> 2         1
> 2         1
> 2
> 2
> 3
> 3
> 3
> 3
>
> I have a record ID, naturally, in the table1, but I don't have an ID
> related field in the table2 (an external file).
>
> With the procedural way this is easy, but with SQL?
> Is possible this ? How?
>
> Any hint is appreciated,
>
>
> Thanks in advance,
>
> Stefano Monterisi
>
>
>

No, you can't do that.

An alternative would be to add one more field - an autoinc field, perhaps.
Then you could select all the records with Key = 1, get the autoinc value
for the ones you want to update, and then use an UPDATE statement with a
WHERE clause with an IN list that holds the autoinc values for the records
you want to change.

The problem is that you have a table where the records cannot be uniquely
identified.

"Stefano Monterisi" <info@sesamoweb.it> wrote in message
news:EA4014F5-959F-4972-92D6-4C141E8207BC@news.elevatesoft.com...
>
> Hi,
> I have the needs to  UPDATE only the first row with a SQL statement;
>
> UPDATE table1 set flagx = 1 where Key in (select key from table2 where
> flagy = 2);
>
> Example data in table2:
>
> Key   flagy
> 1         2
> 1
> 1         2
> 2         2
> 2
> 2         2
> 3
> 4
> 5
>
> Example data in table1
>
> key   flagx
> 1
> 1
> 1
> 1
> 1
> 2
> 2
> 2
> 2
> 3
> 3
> 3
> 3
>
> Now, only the first 2 rows in table1 with key = 1 must have flagx with
> value 1; I need to update only the first row (with the key =1)  that don't
> have value in flagx must be updated; So, with Update statement I need to
> obtain:
>
> table1
> key     flagx
> 1          1
> 1          1
> 1
> 1
> 1
> 2         1
> 2         1
> 2
> 2
> 3
> 3
> 3
> 3
>
> I have a record ID, naturally, in the table1, but I don't have an ID
> related field in the table2 (an external file).
>
> With the procedural way this is easy, but with SQL?
> Is possible this ? How?
>
> Any hint is appreciated,
>
>
> Thanks in advance,
>
> Stefano Monterisi
>
>
>

"Alan Questell" <alanq@pinehurst.net> wrote in message
news:EF9F287F-845A-41DF-8522-92036EF6F6B5@news.elevatesoft.com...
> No, you can't do that.

How to get two records (in this case the last two), as long as you can
sequence the SQL select:

select acct, id into memory\temp from table order by ID desc;
alter table memory\temp add newid autoinc;
select * from memory\temp where newid < 3;

Robert

Hi Robert,

> Sorry, but had trouble understanding your problem here. Do you want to
> update one row for each key that has a match in table2? Or for all the
> matches with table2?
>

I want update only the first row (tha have flagx = null) in table1 that have
a match in table 2;

Stefano Monterisi

Robert,

> select acct, id into memory\temp from table order by ID desc;
> alter table memory\temp add newid autoinc;
> select * from memory\temp where newid < 3;

the problem is that my example is easy; The real situation instead involve
thousands of records!!!!
Not easy, true?
I think that there is no solution with SQL; it's possible with a rich set of
SQL language (like Interbase in stored procedures) that permits to loop with
FOR SELECT statement row by row; Mysql has a nice UPDATE with LIMIT TO x,
that permits to update only the firsts x occurences (this is a message for
Tim );

Thank you, Robert.

Stefano Monterisi

Hi Alan,

> No, you can't do that.
>
> An alternative would be to add one more field - an autoinc field, perhaps.
> Then you could select all the records with Key = 1, get the autoinc value
> for the ones you want to update, and then use an UPDATE statement with a
> WHERE clause with an IN list that holds the autoinc values for the records
> you want to change.
>
> The problem is that you have a table where the records cannot be uniquely
> identified.

I agree with you; My hope was that Tim can pull outside the rabbit from the
hat

Good job and thank you.

Stefano Monterisi

Stefano,

<< I think that there is no solution with SQL; it's possible with a rich set
of SQL language (like Interbase in stored procedures) that permits to loop
with FOR SELECT statement row by row; Mysql has a nice UPDATE with LIMIT TO
x, that permits to update only the firsts x occurences (this is a message
for Tim ); >>

ElevateDB will allow you to do cursor operations (UPDATE WHERE CURRENT OF)
like this in stored procedures.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> ElevateDB will allow you to do cursor operations (UPDATE WHERE CURRENT OF)
> like this in stored procedures.
>

you want to say that I will have to use EDB within  my next  (VS 2005) C#
application instead MSSQL 2005 ?

Stefano Monterisi



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> ha scritto nel
messaggio news:3A4A6D81-9E5A-4375-95E4-9663AA72B640@news.elevatesoft.com...
> Stefano,
>
> << I think that there is no solution with SQL; it's possible with a rich
> set of SQL language (like Interbase in stored procedures) that permits to
> loop with FOR SELECT statement row by row; Mysql has a nice UPDATE with
> LIMIT TO x, that permits to update only the firsts x occurences (this is a
> message for Tim ); >>
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Stefano,

<< you want to say that I will have to use EDB within  my next  (VS 2005) C#
application instead MSSQL 2005 ? >>

Well, I'm certainly not implying that EDB is on the same level as SQL Server
2005, but it does have some pretty nice SQL support.

--
Tim Young
Elevate Software
www.elevatesoft.com