SQL Cross join

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chettiar - 13 Apr 2006 15:02 GMT

I have a SQL Server code which goes like this:

UPDATE ms
SET c1 = ms.c1 + ur.c1
FROM test ms
CROSS JOIN
test ur
WHERE ms.c2 = 'a'
AND ur.c2 = 'b'

Can someone please help me with the equivalent db2 code??

I am facing problems because of this.

Reply to this Message

Serge Rielau - 13 Apr 2006 17:18 GMT

> I have a SQL Server code which goes like this:
>
[quoted text clipped - 9 lines]
>
> I am facing problems because of this.

, <comma> :-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Serge Rielau - 13 Apr 2006 17:23 GMT

> I have a SQL Server code which goes like this:
>
[quoted text clipped - 9 lines]
>
> I am facing problems because of this.

MERGE INTO test ms USING test ur
ON ms.c2 = 'a' and ur.c2 = 'b'
WHEN MATCHED THEN UPDATE SET c1 = ms.c1 + ur.c1

This is a strange example btw...

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

chettiar - 13 Apr 2006 19:17 GMT

SQL0788N The same row of target table "ADWINST1.TEST" was identified
more than once for an update, delete or insert operation of the MERGE
statement. SQLSTATE=21506

Thats the error I get when I am executing this query of yours.

Reply to this Message

Serge Rielau - 13 Apr 2006 20:56 GMT

> SQL0788N The same row of target table "ADWINST1.TEST" was identified
> more than once for an update, delete or insert operation of the MERGE
> statement. SQLSTATE=21506
>
> Thats the error I get when I am executing this query of yours.

That's why I called the example weird....

Please provide some sample data along with the result you're getting
on SQL Server.
I wonder whether SQL server is doing what you expect it to do.
SQL Server does not protect you from updating the same row twice
resulting in non deterministic results.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

chettiar - 13 Apr 2006 21:05 GMT

the data is as follows

1 a
1 b
1 a
1 c
2 a
2 b

The sql server updates it properly. All I need is a way I can do it in
DB2.

Reply to this Message

Serge Rielau - 13 Apr 2006 21:29 GMT

> the data is as follows
>
[quoted text clipped - 7 lines]
> The sql server updates it properly. All I need is a way I can do it in
> DB2.

Define properly! Give us the result.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Artur - 15 Apr 2006 00:23 GMT

Cross joins create a Cartesian product, so what do you expect?

update ms
set c1 =
(
select sum(ms.c1+ur.c1) from ms,ur
where ms.c2 ='a' and ur.c2 = 'b'
)
where c2 = 'a'
;

-- Artur Wronski

Reply to this Message

REMOVE_BEFORE_REPLYING_dportas@acm.org - 16 Apr 2006 17:44 GMT

> the data is as follows
>
[quoted text clipped - 7 lines]
> The sql server updates it properly. All I need is a way I can do it in
> DB2.

SQL Server may have done this properly in your view but it certainly
isn't guaranteed to do so reliably. More than one value is returned
from the join on the right hand side of the = assignment. The correct
result from the UPDATE is therefore officially undefined in SQL Server.
So if your code works for you today it is probably more by luck than by
design.

This is sloppy and dangerous code. Since it doesn't give any
well-defined result you'd better respecify exactly what result you
expect if you need help to port it.

--
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
--

Reply to this Message

chettiar - 20 Apr 2006 08:06 GMT

Actually my problem is I am migrating the SQL Server Code into DB2. So
I am not pretty sure how I can go about doing the cartesian product
update.

Thats the reason I need help.

The data in SQL Server is like i already gave.

c1 c2
--- -----
1 a
1 b
1 a
1 c
2 a
2 b

> > the data is as follows
> >
[quoted text clipped - 30 lines]
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --

Reply to this Message

David Portas - 20 Apr 2006 11:37 GMT

> Actually my problem is I am migrating the SQL Server Code into DB2. So
> I am not pretty sure how I can go about doing the cartesian product
[quoted text clipped - 12 lines]
> 2 a
> 2 b

What I said before. No-one can give you the equivalent DB2 code because
your code is broken. Even in SQL Server the result is undefined
(although you may be lucky and get some result that you like). So you
need to give a better specification.

--
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
--

Reply to this Message

Serge Rielau - 20 Apr 2006 12:16 GMT

> Actually my problem is I am migrating the SQL Server Code into DB2. So
> I am not pretty sure how I can go about doing the cartesian product
[quoted text clipped - 12 lines]
> 2 a
> 2 b

We need the expected OUTPUT.
What do you want the end result to be?

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message