UPDATE statement
-
Hi
Please help me with this statement:
update poz set tip = 'k' where tip
(select p.tip from poz p
inner join juridic j on p.con=j.con
where j.dosar='p')
where is wrong and how can I do this?
-
UPDATE poz
SET tip='k'
FROM pos INNER JOIN juridic ON poz.con = juridic.con
WHERE juridic.dosar='p'
-
It's a very good idea! Thank you very much!
-
UPDATE poz
SET tip='k'
FROM pos , juridic
pos.con = juridic .con
and juridic .dosar='p'
-
If you are going to do it Joe's way then perhaps
UPDATE Poz
SET tip = 'k'
WHERE EXISTS
(SELECT TOP 1 J.con
FROM Juridic AS J
WHERE Poiz.con = J.con
AND J.dosar = 'p');
I was taught that correlated sub-queries were a an evil best avoided if at all possible because they require a query per row in the master table.
-
Joe, does EXISTS short out SELECT * FROM..?
If you are programming for SQL Server then why ignore valid SQL statements?
If I am going to develop for cross platform compatibility then yes, avoid TOP, avoid the funny FROM stuff in UPDATE statements.
As far as I am concerned ignoring Microsoft's extensions, when you are developing in a Microsoft environment is like refusing to use a ring spanner because the standard tool kit only comes with an open ended spanner.
-
OK, so EXISTS short circuits. Great. One less thing to worry about.
I would hardly call using stuff that has been in MS SQL Server since version 7 (and documented) a kludge. The joins in the UPDATE statement have certainly been there since SQL 6.5.
I design my databases so that they will upscale, but I don't design them to port across to other DBMS's, mainly because it isn't cost effective and I don't have exposure to those other DBMS's.
Let us, for the sake of argument, say that DBMS's share 60% common ground, does this mean that we should only ever limit ourselves to that 60%?
-
Nice debate Joe.
Short circuiting has been true of in every SQL you know about. I didn't know but I do now. Perhaps I should buy some of your books when "she who must be obeyed" unmaxes my credit card.
We will just have to agree to disagree on the kludge thing. As far as I am concerned if its been around for 10 years then it's fair game. I would agree with you if it was from beta release or version 1.0 release.
Sybase, hate it.
Your example FROM statement is something I simply wouldn't run. I might update dbo.BAR with dbo.FOO but never the other way around. I think we can both agree that there are somethings that you just don't do and updating the "one" side of a "one to many" relationship is one of them
How do I know it isn't cost effective?
Well I learnt the basics of SQL pretty quickly, but the subtleties are the bits that take the time. It would take a considerable amount of time to get myself to the same level with other DBMS's. Personally I like class room training courses. I can learn more in a classroom full of human beings in 10 minutes than I can from a text book in 1 hour, but that is just me. It is also a very expensive approach. Different people take different approaches and their perspective often takes me down a road I wouldn't normally have considered.
Where do you stand on embedding the .NET CLR within SQL2005?
What about the XML extensions to SQL Server?
How do you classify DTS? I worked with an ex-ORACLE guy and the look on his face when he saw DTS was like someone who has experienced their first 3-in-a-bed!
Where do you draw the line between what is a valuable extension to a tool and what is a break from standards? Standards evolve from a group of people agreeing that "hey, thats a pretty good idea" but it takes time to evolve standards and it takes someone to make the first determination that the existing standard doesn't go far enough.
If I follow your standards argument to its logical conclussion doesn't that mean that the world stops at SQL-92?
I liked your car analogy with the different sized nuts and bolts. Particulary pertinent when America uses Imperial measures (God the irony) for engineering and Europeans use metric.
-
I too have misgivings about the CLR. I've read somewhere that there are people in Microsoft who have misgivings. Something to do with a badly written piece of code being able to bring down the whole server.
I started using SQL Server 6.5 and at that time I was told that SyBase was effectively SQL Server running on UNIX.
As you say, SQL Server evolved where as Sybase...lets just say I have ongoing support headaches with it.
I used to have an overnight job that took 17+ hours on SQL6.5 and often failed. When I first got SQL 7 I installed it on an old machine roughly half the power of my live box and started running tests. When SQL 7 finished the identical job in 45 minutes even though it was running on a worn out PC I was flabbergasted.
I checked the results several times because I simply couldn't believe it.
Switching between SQL2000 and Sybase ASE feels like getting of the French TGV train for a connection to Stephenson's Rocket. It could be that Sybase has everything in it that SQL2000 has but even the Sybase of BOL seems clunky some how.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply