Update SQL syntax
Hi,
I always wonder why this UPDATE SQL doesn't have FROM clause.
I wish the syntax to be like:
UPDATE t1
FROM t2
SET t1.col1 = t2.col2
WHERE t1.fk1 = t2.pk1
...
rather than:
UPDATE t1
SET t1.col1 = (
SELECT t2.col2
FROM t2
WHERE t2.pk1 = t1.fk1)
WHERE exists (
SELECT 'x'
FROM t2
WHERE t2.pk1 = t1.pk1)
Is there any difficulty in implementing such easier syntax in SQL?
or may be my thought is vertical, and could not think of other
complications involved.
Many thanks,
Mahesh.
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Hi Mahesh,
I don't think it should be difficult because it is a valid syntax in Sybase
and MS SQL Server and it is much faster than the embedded select.
Thanks,
Imran.
===========================================
Imran Hussain
MCP, MCSD
imranh@imranweb.com
FOR FREE Software http://www.imranweb.com/freesoft
===========================================
Mahesh wrote:
> Hi,
>
> I always wonder why this UPDATE SQL doesn't have FROM clause.
> I wish the syntax to be like:
>
> UPDATE t1
> FROM t2
> SET t1.col1 = t2.col2
> WHERE t1.fk1 = t2.pk1
> ...
>
> rather than:
>
> UPDATE t1
> SET t1.col1 = (
> SELECT t2.col2
> FROM t2
> WHERE t2.pk1 = t1.fk1)
> WHERE exists (
> SELECT 'x'
> FROM t2
> WHERE t2.pk1 = t1.pk1)>
> Is there any difficulty in implementing such easier syntax in SQL?
> or may be my thought is vertical, and could not think of other
> complications involved.
>
> Many thanks,
> Mahesh.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
--
> UPDATE t1
> FROM t2
> SET t1.col1 = t2.col2
> WHERE t1.fk1 = t2.pk1
this is called an update join and it is valid in every major database except
Informix : )
Gotta use the correlated update sub-queries in informix. I use temp tables.
Mahesh <maheshg@my-dejanews.com> wrote in message
news:7fp1df$20n$1@nnrp1.dejanews.com...
> Hi,
>
> I always wonder why this UPDATE SQL doesn't have FROM clause.
> I wish the syntax to be like:
>
> UPDATE t1
> FROM t2
> SET t1.col1 = t2.col2
> WHERE t1.fk1 = t2.pk1
> ...
>
> rather than:
>
> UPDATE t1
> SET t1.col1 = (
> SELECT t2.col2
> FROM t2
> WHERE t2.pk1 = t1.fk1)
> WHERE exists (
> SELECT 'x'
> FROM t2
> WHERE t2.pk1 = t1.pk1)>
> Is there any difficulty in implementing such easier syntax in SQL?
> or may be my thought is vertical, and could not think of other
> complications involved.
>
> Many thanks,
> Mahesh.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own