Update Set by Subquery - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update Set by Subquery

Author

Topic

mystical
Starting Member

10 Posts

Posted - 2011-05-12 : 13:44:57

I am very new to SQL and hope somebody can be of assistance. I have tried doing a search on the Forum for “Update Set by Subquery” but the results I found were too difficult for me to follow. Hopefully my query falls in to the category of very easy.

I have two Table ABC (contents Cust_num, Date_Accnt_Closed, Other_Fields) and table XYZ (Contents Cust_num, rejection_date, Other_Fields)

In simple english, I want to copy the “rejection_date” from XYZ so it updates Date_Accnt_Closed in table ABC, but only to do so where the Cust_num ’s match and there is a date in the rejection_date field to start with.

I have spent much of the day trying to achieve this simple objective, the code below is the last of very many tries

update tableABC
set Date_Accnt_Closed = (z.rejection_date,

(select
z.rejection_date

from
tableABC a
left join
tableXYZ z
on
z.Customer_Number= a.Customer_Number

where z.rejection_date is not null))

Any help gratefully received.

Bob

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 14:43:12

You don't necessarily have to have a subquery. The following should do it for you. I am making the assumption that rejection_date and Date_Acct_closed are both of the same data type, presumably one of the date/time types such as datetime, date etc.


UPDATE abc SET
   Date_Accnt_Closed = xyz.rejection_date
FROM
   abc 
   INNER JOIN xyz ON abc.Cust_num = xyz.Cust_num
WHERE
   xyz.rejection_date IS NOT NULL

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-12 : 16:53:57

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Let's get rid of your vague narrative and use DDL.

CREATE TABLE Half_Customer_History
(cust_num CHAR(10) NOT NULL PRIMARY KEY,
closure_date DATE, –-- null means still open
..);

CREATE TABLE More_customer_History
(cust_num CHAR(10) NOT NULL PRIMARY KEY,
rejection_date DATE,
..);

This is totally wrong; the design error even has the name: “attribute splitting” Do you split “Personnel” into “Male_Personnel” and “Female_Personnel”, using sex_code as the splitter?

DRI for Transition Constraints

A transition constraint says that an entity can be updated only in certain ways. These constraints are often modeled as a state transition diagram. There is an initial state, flow lines that show what are the next legal states, and one or more termination states. As a very simple example, consider your marital life. Notice that we have to start with Born and you are single. It is important to have one initial state, but you can have many termination states. For example, after you are born, you can die or get married, but you have to be married to get a divorce. The state diagram is shown in on the right.

In this example, we have only one termination state, Dead. Let’s start with a table skeleton and try to be careful about the possible states of our life:

CREATE TABLE Inventory
(product_id CHAR(15) NOT NULL PRIMARY KEY,
..);
CREATE TABLE Orders
(order_nbr CHAR(15) NOT NULL PRIMARY KEY,
product_id CHAR(15) NOT NULL
REFERENCES Inventory (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

We are being good programmers, using a DEFAULT and a CHECK() constraint, but this does not prevent us from turning Born directly to Dead, converting Divorced to Married, and so on. You can actually use CHECK () constraints to enforce our state diagram, but you have to store the current and previous states:

CREATE TABLE MyLife
(..
previous_state VARCHAR(10) NOT NULL,
current_state VARCHAR(10) DEFAULT 'Born' NOT NULL,
CHECK (CASE WHEN (previous_state = 'Born'
AND current_state IN ('Married', 'Born', 'Divorced'))
THEN 'T'
WHEN (previous_state = 'Married'
AND current_state IN ('Divorced', 'Dead'))
THEN 'T'
WHEN (previous_state = 'Divorced'
AND current_state IN ('Married', 'Dead'))
THEN 'T' ELSE 'F' END = 'T')
..);

In effect, the state diagram is converted into a search condition. This procedure has advantages; it will pass information to the optimizer, will port, and will usually run faster than procedural code.

Another declarative way to enforce Transition Constraints is put the state transitions into a separate table and then reference the legal transitions. This requires that the target table have both the previous, and the current, state in two columns. Using this example, we would have something like this:

CREATE TABLE StateChanges
(previous_state VARCHAR(15) NOT NULL,
current_state VARCHAR(15) NOT NULL,
PRIMARY KEY (previous_state, current_state));

INSERT INTO StateChanges
VALUES ('Born', 'Born'), -- initial state
('Born', 'Married'),
('Born', 'Dead'),
('Married', 'Divorced'),
('Married', 'Dead'),
('Divorced', 'Married'),
('Divorced', 'Dead'),
('Dead', 'Dead'); -- terminal state

--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

mystical
Starting Member

10 Posts

Posted - 2011-05-13 : 06:42:20

I have tried as above

UPDATE u_ukul_martdb.LB_MORE_BL_Price_Incentive SET

Date_Accnt_Closed = x.rejection_date

FROM

u_ukul_martdb.LB_MORE_BL_Price_Incentive a

INNER JOIN u_ukul_martdb.LB_MORE_BL_accepts x ON a.Cust_num = x.Cust_num

WHERE

x.rejection_date IS NOT NULL

but i keep getting an error message of
3706 SYNTAX error; expecrting something between thw word "rejection_date" and the "FROM" keyword.

Any suggestions as to what might be missing.??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 07:18:56

What version of SQL are you running? Your query looks perfectly fine on my SQL 2008. Are you running it from SSMS, or from an outside client?

mystical
Starting Member

10 Posts

Posted - 2011-05-13 : 07:44:32

teradata SQL assistant

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-13 : 07:47:13

It must have something to do with Teradata - I will defer to someone else on the forum who is familiar with it; I have no experience with it at all.