Update statement adding case in JOIN is it possibl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update statement adding case in JOIN is it possibl

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-25 : 13:34:29

I want to use the following Update query, is it possible to use a case or if condition in the join:

update tableActions
set logtypeid = t2.logtypeid
from tableactions t1

if tableactions.module='RM' then

Join tableRM t2 on tableactions.moduleid = tableRM.RMID

else if tableactions.module='RF' then

Join tableRF t2 on tableactions.moduleid=tableRF.rfid

Thank you very much for the helpful info.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-01-25 : 13:45:49

Like this

from tableactions t1
inner join tableRM t2  
on t1.ModuleId = Case When t1.Module = 'RM' then t2.RMID
                      When t1.Module = 'RF' then t2.rfid Else .... End

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-25 : 15:47:12

You have so many misconceptions and basic errors.

Hopefully, you did not actually put the word "table" in the table names. But you did not know that there is no such thing as a "type_id", so maybe you did violate ISO-11179 rules and basic data modeling in your design. Think about how silly "blood_type_id" would be as a data element name. It is just "blood_type" -- there is no "id" to it.

We do not use the proprietary and dangerous UPDATE.. FROM.. now that T-SQL has ANSI/ISO Standard MERGE Statements. Google and find out why it does not work.

But you completely missed the basic concept of declarative programming when you invented your own language. There is no control flow, so there are no IF-THEN-ELSE constructs. This is the wrong mindset.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

I have the feeling that you have an attribute split in your design. We could do a kludge with a UNION on the RF (Radio Frequency?) and RM tables, but that only postpones the disaster.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-25 : 16:08:10

Mr. Celko thank you for your greeeat suggestions.

I was only looking for an example. you had so much time to find spelling mistakes to investigate.

I did'nt put my original queries what i am using in my project.

The other user immedeately helped me, thank you very much sodeep.

MR. Celccco only trying my spelling mistakes.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-25 : 16:09:07

quote:
Originally posted by cplusplus

Mr. Celko thank you for your greeeat suggestions.

I was only looking for an example. you had so much time to find my spelling mistakes.

I did'nt put my original queries what i am using in my project.

The other user immedeately helped me, thank you very much sodeep.

MR. Celccco only trying my spelling mistakes.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-25 : 18:27:24

The things I pointed out are not spelling mistakes. They are fundamental errors in RDBMS concepts.
My guess is that RF and RM are actually values for a data source type that has been split into separate tables.
Are you going to whine, or post DDL?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2011-01-28 : 15:57:25

quote:
Originally posted by jcelko

Are you going to whine, or post DDL?

Joe, why so rude? Are you going to be insulting, or helpful?

I realize you have several books to your credit, and are something of an expert in the area of SQL. However, that's no excuse to treat people trying to learn with disrespect. You should know better, I'd think. I mean, you haven't always known everything you know now, and had to learn at some point. (Or maybe I'm wrong, and you were gifted with DDL and DML embedded in your brain in the womb; in that case, I apologize, and you have my sincere sympathy.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources