Update 1 table from 2 others

[Schaeffrcc]

I am new to Tek-Tips and this has been a very useful site.

I am trying to update one table from 2 others Then once updated I planned to use BCP to export the table to a .CSV file.

2 Data Tables
-Agent
-County

1 Table to be updated
-CaseManager

This is the Syntax that I have been using:

UPDATE CaseManager

SET CaseManager.agent_seq = agent.agent_seq,
CaseManager.Agent_Name = (agent.Agent_last_name) & ", " & (agent.agent_first_name) & " " & (agent.agent_middle_name),
CaseManager.agent_active = agent.agent_active,
CaseManager.agent_phone = agent.agent_phone,
CaseManager.agent_initials = agent.agent_initials,
CaseManager.county_name = county.county_name

From Agent

LEFT JOIN county ON agent.county_seq=county.county_seq
LEFT JOIN CaseManager ON agent.agent_seq=CaseManager.agent_seq

and this is the result that I am getting:

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals varchar.

I have been pulling what little hair I have out with this one and it will probably be a basic Syntax issue.

I can do this exact Update Query in Access and have no problems so I do not believe that it is a Field type issue.

Thanks for any help that you can give me.

Thomas

 

[zhavic]

The error is in (agent.Agent_last_name) & ", " & (agent.agent_first_name) & " " & (agent.agent_middle_name)

When you want concatenate strings you must use '+' operator ( a '&' operator is for bitwise AND operations in MSSQL )

SET CaseManager.agent_seq = agent.agent_seq,
CaseManager.Agent_Name = (agent.Agent_last_name) + ', ' + (agent.agent_first_name) + ' ' + (agent.agent_middle_name),
CaseManager.agent_active = agent.agent_active,
CaseManager.agent_phone = agent.agent_phone,
CaseManager.agent_initials = agent.agent_initials,
CaseManager.county_name = county.county_name

From Agent

LEFT JOIN county ON agent.county_seq=county.county_seq
LEFT JOIN CaseManager ON agent.agent_seq=CaseManager.agent_seq


Also, I thing, the last join may be the WHERE clause
See this:

SET CaseManager.agent_seq = agent.agent_seq,
CaseManager.Agent_Name = (agent.Agent_last_name) + ', ' + (agent.agent_first_name) + ' ' + (agent.agent_middle_name),
CaseManager.agent_active = agent.agent_active,
CaseManager.agent_phone = agent.agent_phone,
CaseManager.agent_initials = agent.agent_initials,
CaseManager.county_name = county.county_name

From Agent

LEFT JOIN county ON agent.county_seq=county.county_seq
WHERE agent.agent_seq = CaseManager.agent_seq

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Schaeffrcc]

Thanks for that Help "Zhavic", the "+" replacing the "&" solved that error now I get the following error:

Server: Msg 553, Level 16, State 1, Line 1
UPDATE over nullable side of outer join query on table 'CaseManager'.

Just to try to give you as much information, CaseManager is a new Table that joins to Agent and county joins to Agent so CaseManager can get a piece of data out of the county table. It is possible that a new record could be entered into Agent that I would want added to CaseManager through this Query.

I hope this helps,
Thanks again for the Insight.

Thomas

 

[zhavic]

Try this:
( this is the simplest solution, that inserts new rows into CaseManager
and that updates all rows, ofcourse that was inserted too )

/* insert new rows */
INSERT INTO CaseManager
SELECT agent.agent_seq,
(agent.Agent_last_name) + ', ' + (agent.agent_first_name) + ' ' + (agent.agent_middle_name),
agent.agent_active,
agent.agent_phone,
agent.agent_initials,
county.county_name
FROM agent
LEFT JOIN county ON county.county_seq = agent.agent_seq
LEFT JOIN CaseManager ON CaseManager.agent_seq = agent.agent_seq
WHERE CaseManager.agent_seq IS NULL -- select recors, that are not in CaseManager

/* update all rows ( old rows an also that was inserted above */
UPDATE CaseManager
SET CaseManager.agent_seq = agent.agent_seq,
CaseManager.Agent_Name = (agent.Agent_last_name) + ', ' + (agent.agent_first_name) + ' ' + (agent.agent_middle_name),
CaseManager.agent_active = agent.agent_active,
CaseManager.agent_phone = agent.agent_phone,
CaseManager.agent_initials = agent.agent_initials,
CaseManager.county_name = county.county_name
From CaseManager
LEFT JOIN agent ON agent.agent_seq = CaseManager.agent_seq
LEFT JOIN county ON agent.county_seq=county.county_seq


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Schaeffrcc]

Zhavic, your wisdom is only exceeded by your generosity with it, or in other words that worked excellent. Now for the final follow up question for you or any of our other fine viewers.

Now that I have a Query that can update my table with the correct data that I need, is a scheduled nightly run of BCP the best way to export that data out to a .CSV file with “~” delimiters or is there an easy way to export this data directly to a .CSV file without the 2 steps.

Thanks for any ideas,

Thomas.

 

[Schaeffrcc]

The code from zhavic posted works great but now I am having a problem whan I put it into a Stored Procedure in SQL, if there is a NULL value in any of the 3 fields Client_Last_name, Client_First_Name, or Client_Middle_Name then it makes the whole field a null value.

I was thinking if I could do an IF Then Statement and say if Null then enter ' ' else put in the actual value but can't get the Syntax to work.

Thanks for your help,

Thomas.

 

[JamesLean]

Two options:

1) Add the line

SET CONCAT_NULL_YIELDS_NULL OFF

to the top of your proc.

2) Change the query to:

CaseManager.Agent_Name = COALESCE(agent.Agent_last_name, '') + ', ' + COALESCE(agent.agent_first_name, '') + '  ' + COALESCE(agent.agent_middle_name, '')

--James

 

[palagrim]

Hi,

Firstly, you'll have to excuse me - I'm brand new here and a bit of a newbie when it comes to SQL... I have had a look around the forum and used the search, but can't find the solution I need.

Anyway, here's my problem:

I have 2 tables:

Table 1:

<id> | <text> | <text> |Status (int) | <text>
---------------------------------------
1 | Blah | Blah | 4 | Blah
2 | Text | Text | 2 | Text
3 | More | More | 7 | More
4 | Text | Text | 2 | Text

Table 2:

<id> | <text>
-------------
1 | Text
2 | More
3 | Text

etc etc...

The idea is that Table 2's "id" is a reference to what the status code in Table 1 is.

I need to replace the status code in table 1 with the text related to the id in table 2... If that makes sense, my explanations are about as good as my SQL.

I appreciate that this is probably a dead simple query but I'd appreciate any help?

Thanks.

 

[JamesLean]

SELECT t1.id,
  t2.text AS status,
  (other cols...)
FROM table1 t1
  JOIN table2 t2 ON t1.status = t2.id

BTW, you should probably have started a new thread for this question.

--James

 

[palagrim]

A million thanks yous!

And yeah, I thought I was on a different page... So many browser windows, so little time ;-)

Thanks again,

Craig