SQL Cross join

[chettiar]

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.

[Serge Rielau]

chettiar wrote:

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.

, <comma> :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

chettiar wrote:

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.

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

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[chettiar]

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.

[Serge Rielau]

chettiar wrote:

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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[chettiar]

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.

[Serge Rielau]

chettiar wrote:

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.

Define properly! Give us the result.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Artur]

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

[REMOVE_BEFORE_REPLYING_dportas]

chettiar wrote:

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.

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/m...S,SQL.90).aspx
--

[chettiar]

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


RE****************************@acm.org wrote:

chettiar wrote:

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.

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/m...S,SQL.90).aspx
--

[David Portas]

chettiar wrote:

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

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/m...S,SQL.90).aspx
--

[Serge Rielau]

chettiar wrote:

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

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

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab