IT Gal
asked on
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!
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!
ASKER
"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.
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.
No you ned an INNER JOIN. What interface are you using to create/Amend this SQL?
ASKER
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.
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.
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
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
ASKER
"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.
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.
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?
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?
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You missed to pass parameter to identify single row. thats why it updates all.
ASKER
@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.
@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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
can you provide the screenshot,where you executing query....
Bhavesh
can you provide the screenshot,where you executing query....
Bhavesh
ASKER
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!
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!
ASKER
Thanks to all posters!
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.
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.
ASKER
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?
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_Exp
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
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_Exp
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
Kelvin