SQL Update statement makes all records the same value

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!
IT GalAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Kelvin SparksCommented:
Remove the cross join Member part, and it should work and change Member_1 to Member everywhere.

Kelvin
0
 
IT GalAuthor Commented:
"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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Kelvin SparksCommented:
No you ned an INNER JOIN. What interface are you using to create/Amend this SQL?
0
 
IT GalAuthor Commented:
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
 
Kelvin SparksCommented:
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
 
IT GalAuthor Commented:
"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
 
Kelvin SparksCommented:
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
 
IT GalAuthor Commented:
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
 
Kelvin SparksConnect With a Mentor Commented:
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
 
Alpesh PatelAssistant ConsultantCommented:
You missed to pass parameter to identify single row. thats why it updates all.
0
 
IT GalAuthor Commented:
@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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

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

Bhavesh
0
 
IT GalAuthor Commented:
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
 
IT GalAuthor Commented:
Thanks to all posters!
0
 
Scott PletcherSenior DBACommented:
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
 
IT GalAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.