?
Solved

SQL Update statement makes all records the same value

Posted on 2012-09-19
18
Medium Priority
?
400 Views
Last Modified: 2012-09-20
Ok, so I have a database with member information in it. One of the fields is a license expiration date.

I get an update file from a regulatory agency that has updated values for this expiration date. That file contains a unique ID that I can key off of.

So I update this file into its own table in my database, and then proceed to do an update to that table using this logic: (names changed to protect the innocent)

UPDATE MEMBER
SET License_Expire_Date = MynewTable.ExpDate
FROM MyNewTable
JOIN MEMBER on MynewTable.[unique ID] = Member.unique_id

SQL 2005 then changes this logic to:

UPDATE    Member
SET              License_Expire_Date = MynewTable.ExpDate
FROM         MynewTable INNER JOIN
                      Member AS Member_1 ON MynewTable.[unique ID] = Member_1.unique_ID CROSS JOIN
                      Member

So I execute this, and it hits every row in my database, regardless of whether there's a value in my update file for it or not. AND, it makes all the values the same.

I'm at my wits end. I've tried it a zillion different ways and the result is always the same. It always affects every record and it always makes them all the same, even though my update table has different values for each.

Ideally, I really only want to touch the records where the unique ID's match. I have a full file now, so I can go back and 'fix' this, but I'm obviously doing it wrong. The thing of it is, I had this syntax saved from a time that I had done it previously and it worked fine then. I'm not sure what changed. We're still on the same version of SQL.

Help!
0
Comment
Question by:IT Gal
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38416131
Remove the cross join Member part, and it should work and change Member_1 to Member everywhere.

Kelvin
0
 

Author Comment

by:IT Gal
ID: 38416133
"Remove the cross join Member part, and it should work and change Member_1 to Member everywhere.

Kelvin"

I would love to, but SQL keeps re-inserting it. How do I get it to execute without it changing it?

Even if I just execute it without changing the syntax first, it still modifies it before it runs it. The join it changes it to is indiscriminate. I'm not sure why it's doing this.

Should I be using a Left join instead or something? I'm at a loss.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38416143
No you ned an INNER JOIN. What interface are you using to create/Amend this SQL?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:IT Gal
ID: 38416144
I'm in SQL management studio.

Could you please provide an amended query?

I don't really understand why SQL is creating this temp table or doing the cross join. It's baffling.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38416163
UPDATE    m
SET              License_Expire_Date = n.ExpDate
FROM         MynewTable n INNER JOIN
                      Member m ON n.[unique ID] = m.unique_ID

Should be all that you need.

Kelvin
0
 

Author Comment

by:IT Gal
ID: 38416196
"UPDATE    m
SET              License_Expire_Date = n.ExpDate
FROM         MynewTable n INNER JOIN
                      Member m ON n.[unique ID] = m.unique_ID

Should be all that you need."

Not working. SQL modifies it, adds the MEMBER_1 table and the cross join, and the same thing results.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 38416225
Just in case it is an order issue, try it this way

UPDATE    m
SET              License_Expire_Date = n.ExpDate
FROM         Member  INNER JOIN mMynewTable n
                       ON m.unique_ID = n.[unique ID]

This is syntax I use almost daily - have you any special settings in SQL Server?
0
 

Author Comment

by:IT Gal
ID: 38416284
That then tells me that the field I'm trying to update cannot be updated.

However, I've modified the update logic to update single records by the unique ID, and it can do that. It just won't do them all at once, for some reason.

I'm baffled.
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 400 total points
ID: 38416299
Ah, my typo

UPDATE    m
SET              License_Expire_Date = n.ExpDate
FROM         Member m INNER JOIN MynewTable n
                       ON m.unique_ID = n.[unique ID]
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38416729
You missed to pass parameter to identify single row. thats why it updates all.
0
 

Author Comment

by:IT Gal
ID: 38417338
@Kelvin - I caught the typo before and ran it corrected. It still wouldn't allow me to update it.

@PatelAlpesh - can you provide an example of what you're describing? I have roughly 13000 total records, and 5000 or so in the update table, but it still modifies all 13000 and assigns them all the same value. I just want it to update 1 for 1 where there's a matching unique ID, but it won't seem to do it.

Since I can modify single records if one is specified by unique ID, it makes me wonder if this needs to actually have a subquery that iterates through each record. Seems odd to have to do it that way, but that's all I can think of.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 38418100
Do NOT use any GUI elements to code or run the query.

Instead, click on the "New Query" button, change the db name in the drop down database list at the top to be the db containing the tables, then copy / paste the following code and press F5:

UPDATE MEMBER
SET License_Expire_Date = MynewTable.ExpDate
FROM MEMBER
INNER JOIN MyNewTable ON
    MynewTable.[unique ID] = MEMBER.unique_id
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38418215
Hi,

can you provide the screenshot,where you executing query....

Bhavesh
0
 

Author Comment

by:IT Gal
ID: 38418265
ScottPletcher for the win! Thanks man, that worked perfectly!

So the GUI query screen is what was borking things up, then? I know its crazy but I'm just still not totally used to SQL 2005's Management Studio. I was really used to the old Query Analyzer, and I'm not in it every day to begin with.

Thanks so much!
0
 

Author Closing Comment

by:IT Gal
ID: 38418268
Thanks to all posters!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38418628
Yes, the GUI is flaky for several things, including query writing.

For example, you can't use a CASE in a view definition in the GUI (last time s/o at EE tried), but you can use the Query window directly for that.

In particular, NEVER ALTER A TABLE using the GUI unless:

the table is empty or very small

Instead, use the ALTER TABLE command.

The GUI always copies everything to a new table, then copies it back.  The ALTER statement does not.
0
 

Author Comment

by:IT Gal
ID: 38418641
Yes, the GUI is flaky for several things, including query writing.

For example, you can't use a CASE in a view definition in the GUI (last time s/o at EE tried), but you can use the Query window directly for that.

In particular, NEVER ALTER A TABLE using the GUI unless:

the table is empty or very small

Instead, use the ALTER TABLE command.

The GUI always copies everything to a new table, then copies it back.  The ALTER statement does not.

That's great info. For the record, do you have any advice on to how to wrap something like this in a transaction so it could be rolled back if it didn't have the desired effect?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38418758
Hmm.  You can put it in a transaction, but you can only roll it back if you haven't committed the transaction yet, which means you have to have WITH (NOLOCK) in your query to see it.

For a single UPDATE, the best way to prep it is to do an identical SELECT version first, and if that gives you the results you want, then do the UPDATE.

For example,

-- preview which rows/values the UPDATE will affect
SELECT
    Member.unique_id, Member.License_Expire_Date,
    MyNewTable.ExpDate AS [New_Value_For_License_Expire_Date]
FROM MyNewTable
JOIN MEMBER on MynewTable.[unique ID] = Member.unique_id
ORDER BY
    Member.unique_id

-- review results of above SELECT before issuing UPDATE



Or:


DECLARE @unique_ids TABLE (
    unique_id int --<<-- chg data type to match table
    )

BEGIN TRANSACTION

UPDATE MEMBER
SET License_Expire_Date = MynewTable.ExpDate
    OUTPUT INSERTED.unique_id INTO @unique_ids
FROM MyNewTable
JOIN MEMBER on MynewTable.[unique ID] = Member.unique_id

--/* comment out when the UPDATE will be COMMITTed
SELECT
    'Rows below are AFTER the Update.  ' +
    'This is JUST A DISPLAY.  ' +
    'The current Update will be rolled back.  ' +
    'YOU MUST RE-RUN THE UPDATE TO ACTUALLY MAKE THE CHANGES.'
--*/

SELECT ui.unique_id, m.License_Expire_Date
FROM @unique_ids ui
INNER JOIN MEMBER m ON
    ui.unique_id = m.unique_id

--COMMIT TRANSACTION  --when UPDATE confirmed good, uncomment this, comment ROLLBACK
ROLLBACK TRANSACTION
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question