RE: SQL Gurus Wanted

From: Porterfield, Sean

-----Original Message-----
From: Joe Pluta

From: Porterfield, Sean

UPDATE File1
SET File1.Data1 = File2.Data1
FROM File1, File2
WHERE File1.Key1 = File2.Key1

Should this work correctly? Thanks.

Sean, as far as I know the syntax above won't work at all.

However, without the WHERE EXISTS, the query will update all
records in A.

Thanks, Joe. I created some simple tables to test this out; it helps a
lot to see it in action.

Hah! Great minds think alike! That's what I usually try to do before
answering SQL questins. It's the only way I can be sure.

My version produced "Column qualifier or table FILE2 undefined." Using
yours without the WHERE EXISTS, every record was updated. With the WHERE
EXISTS, I got exactly what I expected.

I guess this is just a syntax difference between MS SQL Server and System
i SQL. Luckily it seems with the subselect and where exists, I can use it
on either platform.

Ah, yes, the myth of "platform independent SQL". Unfortunately, the various
SQL databases have proprietary syntaxes. It's very much like the browser
wars. Until you get an actual standards body to create a standard that
everyone adheres to, there's almost no way to get the vendors to agree.

For SQL, the standard is ANSI SQL. DB2 is as close to ANSI SQL as any
database, and what I've found is that (except for obvious platform-specific
difference such as SYSTEM naming vs. SQL naming) if it runs on DB2 it will
usually run on another database. It doesn't work the same way in reverse;
SQL Server, Oracle, MySQL all have their own non-standard dialects that do
not run on other databases.

Joe