October 18, 2017 at 2:53 pm
I have two tables T_V and T_C. I'm updating T_V's columns with T_C's respective columns. I'm getting an error during compiling the procedure. The error is
Msg 156, Level 15, State 1, Procedure proc1
Incorrect syntax near the keyword 'INNER'.
CREATE PROCEDURE proc1
AS
BEGIN
UPDATE T_V INNER JOIN T_C ON T_V.[Unique ID] = T_C.[Unique ID]
SET T_V.A = 1-[T_C].A, T_V.B = 1-[T_C].B, T_V.C = 1-[T_C].C, T_V.D = 1-[T_C].D;
END
GO
October 18, 2017 at 3:36 pm
You really need to learn about books online. Your UPDATE statement was totally wrong.
UPDATE tv SET
A = 1 - [tc].A
, B = 1 - [tc].B
, C = 1 - [tc].C
, D = 1 - [tc].D
FROM
T_V AS tv
INNER JOIN T_C AS tc
ON tv.[Unique ID] = tc.[Unique ID];
October 20, 2017 at 5:57 pm
soldout6000 - Wednesday, October 18, 2017 2:53 PMI have two tables T_V and T_C. I'm updating T_V's columns with T_C's respective columns. I'm getting an error during compiling the procedure. The error isMsg 156, Level 15, State 1, Procedure proc1
Incorrect syntax near the keyword 'INNER'.CREATE PROCEDURE proc1
AS
BEGIN
UPDATE T_V INNER JOIN T_C ON T_V.[Unique ID] = T_C.[Unique ID]
SET T_V.A = 1-[T_C].A, T_V.B = 1-[T_C].B, T_V.C = 1-[T_C].C, T_V.D = 1-[T_C].D;
END
GO
>> I have two tables T_V and T_C. I'm updating T_V's columns with T_C's respective columns. I'm getting. Incorrect syntax near the keyword 'INNER'. <<
When it's not technically wrong, using the prefix "T_" is a design flaw called a Tibble. It means you're putting metadata in the names. We used have to do this in the old days with one pass compilers, but that was 60 years ago.
I like you to think about doing an update on the join and what it really means. The join produces a new table, so you update that new table which disappears at the end of the session. In short, the syntax makes no sense whatsoever. You also need to learn how to use the new merge statement is been around for about five or six years. Why did you think "unique ID" would be a good variable name? Why did you embed spaces in it? Why the one-letter table names? Why no DDL?
Let's use nonsense names that might actually be ISO-11179 compliant.
MERGE INTO Voobs
USING Cloobs
ON Voobs.something_id = Cloobs.something_id
WHEN MATCHED
THEN UPDATE
SET voobs.a = 1 - cloobs.a,
voobs.b = 1 - cloobs.b,
voobs.c = 1 - cloobs.c,
voobs.d = 1 - cloobs.d;
Please post DDL and follow ANSI/ISO standards when asking for help.
October 20, 2017 at 6:15 pm
jcelko212 32090 - Friday, October 20, 2017 5:57 PMsoldout6000 - Wednesday, October 18, 2017 2:53 PMI have two tables T_V and T_C. I'm updating T_V's columns with T_C's respective columns. I'm getting an error during compiling the procedure. The error isMsg 156, Level 15, State 1, Procedure proc1
Incorrect syntax near the keyword 'INNER'.CREATE PROCEDURE proc1
AS
BEGIN
UPDATE T_V INNER JOIN T_C ON T_V.[Unique ID] = T_C.[Unique ID]
SET T_V.A = 1-[T_C].A, T_V.B = 1-[T_C].B, T_V.C = 1-[T_C].C, T_V.D = 1-[T_C].D;
END
GO>> I have two tables T_V and T_C. I'm updating T_V's columns with T_C's respective columns. I'm getting. Incorrect syntax near the keyword 'INNER'. <<
When it's not technically wrong, using the prefix "T_" is a design flaw called a Tibble. It means you're putting metadata in the names. We used have to do this in the old days with one pass compilers, but that was 60 years ago.
I like you to think about doing an update on the join and what it really means. The join produces a new table, so you update that new table which disappears at the end of the session. In short, the syntax makes no sense whatsoever. You also need to learn how to use the new merge statement is been around for about five or six years. Why did you think "unique ID" would be a good variable name? Why did you embed spaces in it? Why the one-letter table names? Why no DDL?
Let's use nonsense names that might actually be ISO-11179 compliant.
MERGE INTO Voobs
USING Cloobs
ON Voobs.something_id = Cloobs.something_id
WHEN MATCHED
THEN UPDATE
SET voobs.a = 1 - cloobs.a,
voobs.b = 1 - cloobs.b,
voobs.c = 1 - cloobs.c,
voobs.d = 1 - cloobs.d;
Why use MERGE when all you are doing is an UPDATE? Not only that, I have seen the MERGE statement take longer and use more resources than the combination of UPDATE and INSERT (UPSERT).
Just because you can use it doesn't mean you should.
October 21, 2017 at 10:01 am
soldout6000 - Wednesday, October 18, 2017 2:53 PM
Why use MERGE when all you are doing is an UPDATE? Not only that, I have seen the MERGE statement take longer and use more resources than the combination of UPDATE and INSERT (UPSERT). Just because you can use it doesn't mean you should.
The merge is portable and standard. Updating a join is not; it's pure Sybase dialect from the old days, like the quirky update. The merge also expresses the intent of this statement. Be kind of handy when you're trying to maintain your code. I am assuming that it's not insanely horrible as far as performance goes.
The merge on the other hand will catch the cardinality errors that are part of the quirky update. I agree that Microsoft has not done a good job with merge, but perhaps they'll catch up with DB2, Postgres and Oracle in the next releases.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 21, 2017 at 4:13 pm
jcelko212 32090 - Friday, October 20, 2017 5:57 PMsoldout6000 - Wednesday, October 18, 2017 2:53 PM
Why use MERGE when all you are doing is an UPDATE? Not only that, I have seen the MERGE statement take longer and use more resources than the combination of UPDATE and INSERT (UPSERT). Just because you can use it doesn't mean you should.
The merge is portable and standard. Updating a join is not; it's pure Sybase dialect from the old days, like the quirky update. The merge also expresses the intent of this statement. Be kind of handy when you're trying to maintain your code. I am assuming that it's not insanely horrible as far as performance goes.
The merge on the other hand will catch the cardinality errors that are part of the quirky update. I agree that Microsoft has not done a good job with merge, but perhaps they'll catch up with DB2, Postgres and Oracle in the next releases.
It doesn't matter, Joe. He's using SQL Server, not DB2 or other databases. Since SQL Server is the only one of the databases you listed that can actually use a join in an UPDATE, perhaps it's those other 3 database engines that need to catch up with SQL Server. 😉 Since you claim to have been on the team that created Sybase/SQL Server, I'm thinking that you probably shouldn't bitch about proprietary functionality. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2017 at 1:18 pm
Lynn Pettis - Friday, October 20, 2017 6:15 PMjcelko212 32090 - Friday, October 20, 2017 5:57 PMsoldout6000 - Wednesday, October 18, 2017 2:53 PMWhy use MERGE when all you are doing is an UPDATE? Not only that, I have seen the MERGE statement take longer and use more resources than the combination of UPDATE and INSERT (UPSERT). Just because you can use it doesn't mean you should.
The merge is portable and standard. Updating a join is not; it's pure Sybase dialect from the old days, like the quirky update. The merge also expresses the intent of this statement. Be kind of handy when you're trying to maintain your code. I am assuming that it's not insanely horrible as far as performance goes.
The merge on the other hand will catch the cardinality errors that are part of the quirky update. I agree that Microsoft has not done a good job with merge, but perhaps they'll catch up with DB2, Postgres and Oracle in the next releases.
It doesn't matter, Joe. He's using SQL Server, not DB2 or other databases. Since SQL Server is the only one of the databases you listed that can actually use a join in an UPDATE, perhaps it's those other 3 database engines that need to catch up with SQL Server. 😉 Since you claim to have been on the team that created Sybase/SQL Server, I'm thinking that you probably shouldn't bitch about proprietary functionality. 😉
Why do you assume that he's always going to be on only one release of Microsoft SQL Server? The truth is these days. The reason you write portable code is that if you're not using at least two maybe three other SQL engines, then you're waiting to get screwed in the next release of your own product.
One of my proudest moments as a consultant Microsoft was adding the ANSI/ISO standard outer join syntax to replace the old extended equality (*= or += depending on your product). I put the new notation in a comment, and coded the proprietary syntax at the time. Several months later, one of my clients called me back and said they were upgrading and wanted to get rid of the old deprecated outer joins. I told them that all they had to do was comment out the old code, and make my comments live. I got a call back telling me they had done the entire conversion and upgrade in less than a week with testing. But more than that, the same queries had to be replicated in I believe it was Oracle but I don't remember it's been too many years. They just did a cut-and-paste, a little edit and some testing. The estimate is that I save them five – six months of rewriting if it had to do it all from scratch in both databases.
Remember "Larry the cable Guy" from the Jeff Foxworthy redneck comedian show? His slogan was "get her done!", Instead of looking ahead, he would do the most immediate, harmful, expedient thing he could think of to finish his job.
I learn to program for defense systems, and Gus, my old boss, used to make us check our code do all kinds of audits because as he put it, "you make a mistake here, and you kill the wrong people!"; I never got over that model professionalism.
I consulted with Microsoft when they were getting rid of the old Sybase code base and talked with Sybase people who are on the ANSI X3H2 standards committee. I was hardly part of their development group.
Actually, other SQL engines have looked at the quirky update and rejected it. So has ANSI/ISO. It simply doesn't make sense in a consistent SQL data model. It only works because of particular architecture done by one vendor in the very early days of the language (as Jim Gray put it, "we didn't know what we were doing! Our mindset was still stuck with sequential file system based on UNIX.")
Please post DDL and follow ANSI/ISO standards when asking for help.
October 22, 2017 at 2:55 pm
jcelko212 32090 - Sunday, October 22, 2017 1:18 PMWhy do you assume that he's always going to be on only one release of Microsoft SQL Server? The truth is these days. The reason you write portable code is that if you're not using at least two maybe three other SQL engines, then you're waiting to get screwed in the next release of your own product.
That's a bit of hooie for several reasons.
1, Whether you write portable code or not, the makers of RDBMS engines reserve the right to deprecate and discontinue ANY code and have done so quite often.
2. In most cases, people don't promote code backwards. If they have to do such a thing, then it's because of what you said... they have old servers to support. When they do that, then they have to be keenly aware of what they're doing and accommodate for such. It's like having a really old classic car... you'd better know that running unleaded gasoline in it will damage the engine and that you have to add something to it to prevent the damage.
3. Writing code needs to be progressive. If people stuck to only ANSI code or code that worked in SQL Server 6.5, their code would suck for performance, resource usage, and sometimes even accuracy.
4. If you use ANSI as a guide for "portable code", then you might as well stick to using only 4 function calculators because you're missing out on a whole lot of features. It would be usually be stupid at the very least not to use the improvements offered in proprietary extensions of any RDBMS. As you might guess, there are some extensions that just don't make sense. For example, the newer temporal datatypes of DATE, TIME, and DATETIME2() have all been crippled because, unlike the DATETIME datatype, they don't work with the ANSI standard which states the SomeDateTime2 - SomeDateTime1 - Period.
5. Following what is currently "portable" is absolutely no guarantee of portability in the future even when limited to just future revisions of the same product. Take your "proudest moment" from above and realize that changed the standard and broke shedloads of code. Even when you had prepared your client for the change, it still took them a week to make the changes and test them.
Remember "Larry the cable Guy" from the Jeff Foxworthy redneck comedian show? His slogan was "get her done!", Instead of looking ahead, he would do the most immediate, harmful, expedient thing he could think of to finish his job.
Heh... to be honest, that's the first person I think of when you talk about some of your solutions especially when you violate the ANSI standards that you hold so dear by using things like YYYY-MM-00. 😉
I learn to program for defense systems, and Gus, my old boss, used to make us check our code do all kinds of audits because as he put it, "you make a mistake here, and you kill the wrong people!"; I never got over that model professionalism.
Noted and appreciated and, although your example is a bit extreme, it helps me make a point. There is no "truly" portable code not even within the same product but for a different rev especially for the DOD. You can certainly save a bit of development time using "copy'n'paste" of known working and standardized code but you still have to do full regression testing to ensure that the new rev of whatever it is you're using isn't going to break anything. Unfortunately, MS and many other "public" software providers haven't learned that lesson yet.
I consulted with Microsoft when they were getting rid of the old Sybase code base and talked with Sybase people who are on the ANSI X3H2 standards committee. I was hardly part of their development group.
If that's true, then you should try harder to make it not sound like you were.
Actually, other SQL engines have looked at the quirky update and rejected it. So has ANSI/ISO. It simply doesn't make sense in a consistent SQL data model. It only works because of particular architecture done by one vendor in the very early days of the language (as Jim Gray put it, "we didn't know what we were doing! Our mindset was still stuck with sequential file system based on UNIX.")
I suspect that may be because the other systems had made the realization that running totals are a necessary part even of RDBMS and wrote code to easily do running totals. Even MS Access had such a thing even in the very early days. The UPDATE statement in SQL Server is the most robust and flexible UPDATE statement I've seen in many languages. Imagine my angst when I first started programming in Oracle and found out that it didn't have a FROM clause and that you essential had to write code twice to do a joined update... Correlated Sub-Queries in the SELECT list had to basically be repeated as a part of the WHERE clause. The fact that you can use the documented "3-part update" (the "quirky" part of "quirky update") is frosting on the cake and has personally helped me pull off some great code that would have taken hours longer to write and hours longer to run. The proverbial "4 function calculator" that you advocate by stressing "portable code" just wasn't and frequently still isn't going to do the trick.
Tell Mr. Gray to stop caving in to well meaning but uninformed public opinion (if he ever actually said what you said he said). They did a GREAT job on the UPDATE statement, even on the "quirky" part. It's a bloody shame they didn't extend that into the SELECT statement.
Accuracy and performance is in the code... if you let it. Use the proprietary enhancements to the SQL Language available and IF you ever need to migrate to another database or a revision to your current product causes a break in the code, then it's a small price to pay to fix it compared to the amount of time and effort it took to write it instead of doggedly being relegated to paranoid portable code requirements. And, if you're promoting code backwards, you may have more serious problems than just code portability.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2017 at 12:53 pm
It will be hard to tell Jim anything; he is dead. https://en.wikipedia.org/wiki/Jim_Gray_(computer_scientist). You might want to learn what he did with SQL, and databases in the early days.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply