Uodate values based on a query.
-
Hi Everyone,
I am tryingto update a column by inserting into data from a query.
I am trying to use the following query;
UPDATE tb_comments
SET user_id =
(SELECT id_tb_comments, tb_users_crm.id_user
FROM tb_comments
INNER JOIN tb_jobs ON tb_comments.job_id = tb_jobs.id_tb_jobs
INNER JOIN tb_school ON tb_jobs.school = tb_school.id_tb_school
INNER JOIN tb_portfolio ON tb_school.folio_id = tb_portfolio.id_tb_portfolio
INNER JOIN tb_users_crm ON tb_portfolio.id_tb_portfolio = tb_users_crm.id_portfolio
WHERE id_tb_comments = tb_comments.id_tb_comments)
However I get a message about only one expression being allowed in the SELECT without the use of EXISTS
if you have any ideas, I would be much appreciated
Also, I would have added in the code for the creation of the tables, but I don't know how to generate the SQL from tables that already exist.
I am certain there is a tool/script to do it - I just don't know it.
Thanks once again.
Gavin Baumanis
Smith and Wesson. The original point and click device.
-
UPDATE tb_comments
SET user_id =
(SELECT id_tb_comments, tb_users_crm.id_user
FROM tb_comments
INNER JOIN tb_jobs ON tb_comments.job_id = tb_jobs.id_tb_jobs
INNER JOIN tb_school ON tb_jobs.school = tb_school.id_tb_school
INNER JOIN tb_portfolio ON tb_school.folio_id = tb_portfolio.id_tb_portfolio
INNER JOIN tb_users_crm ON tb_portfolio.id_tb_portfolio = tb_users_crm.id_portfolio
WHERE id_tb_comments = tb_comments.id_tb_comments)
One must first ask, which of these two columns did you want to use to set the User_ID column.
My recommendation would be to lookup "UPDATE, UPDATE (described)" in Books Online and take a look at some of the examples there...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
It's amazing what you can find out when you RTFM!
Turns out I was a lot closer than I thought
I orginally only had one column in the select, but it complained about there bing more than one item returned, or some such error
I had a VERY quick chat with a DBA here at work and they gave the syntax that I was trying to use (above).
Anyway, turned out that I needed was;
UPDATE tb_comments
SET user_id = tb_users_crm.id_user
FROM tb_comments
etc...
Note: there is no SELECT reserved word used!
On my secondary "kind of" question, do you know how I can create the SQL required to create a table with EM?
Gavin Baumanis
Smith and Wesson. The original point and click device.
-
Correct... SQL Server's UPDATE statement is one of the more useful ones in that it actually has TWO from clauses that are specifiable... most people leave out the first FROM as it is not required and serves only to confuse. The second FROM clause takes the place of a SELECT and they (your DBA's) gave you one of the many forms of UPDATE in a correct form (although some would argue...)
For your other matter about generating a table script... the best place, in my humble opion, to create the CREATE TABLE statement for an existing table is, in fact, in QUERY ANALYZER... try this... open QA, select the correct database at the top of the window, and then press the {f8} key... behold the marvel of the Object Browser tree! Drill down as you would expect, right click on the table of your choice, and select "Script Object to New Window As..." and then click on "Create". Viola! Works with stored procs, views... in fact, anything and everything that appears in the Object Browser...
If you do the same right click thing again, but, instead, click on "Scripting Options", you can do some pretty good fine tuning as to what the script will look like including getting rid of those damned collation clauses.
A couple of other goodies about QUERY ANALYZER that you should "RTFM" about...
SQL Query Analyzer
SQL Query Analyzer, keyboard shortcuts
... and a bunch more... you can search for objects, tune indexes, and just about anything you want...
Don't forget to try dragging names from the Object Browser into the code window.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I would not be so excited about "getting rid of those damned collation clauses".
Some DBA's don't notice the difference between SQL_Latin1_General_CP1_CI_AI collation and Windows Latin1_General_CI_AI collation.
That's why we have 2 production servers with different default collations. Those servers talk to each other and transfer data in both directions. Of course there are nvarchar columns matchings in almost every transitional query. And if I occasionally forget to mention collation in CREATE TABLE statement (including # tables) or even in WHERE S1.D1.dbo.T1.Name = S2.D2.dbo.T2.Name I get error almost immediately.
_____________
Code for TallyGenerator -
How true... fortunately, I have DBA's that do notice the difference
but it's good to check.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I think you may be looking for a syntax similar to this:
UPDATE tb_comments
SET
user_id=tb_users_crm.id_user,
user_comment =id_tb_comments
FROM tb_users_crm
INNER JOIN tb_jobs ON tb_comments.job_id = tb_jobs.id_tb_jobs
INNER JOIN tb_school ON tb_jobs.school = tb_school.id_tb_school
INNER JOIN tb_portfolio ON tb_school.folio_id = tb_portfolio.id_tb_portfolio
INNER JOIN tb_users_crm ON tb_portfolio.id_tb_portfolio = tb_users_crm.id_portfolio
WHERE id_tb_comments = tb_comments.id_tb_comments
The table names and join fields are a bit muddled but then you know which tables you want to use, the form above addresses your basic problem of updating multiple fields in a single swipe.
Good hunting!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply