CODEWITHSUNDEEP
sqlsql-server-2008sql-update
Derek Foulk
Derek Foulk

Reputation: 1902

Set One Column Equal to Another In Different Tables

I am fairly new to SQL, and I am trying to create an SQL command checks if something is equal to something in one table, then update the value of something in another table. I have looked for solutions online, but I am not quite good enough to determine if what I have seen can be used to accomplish what I want to accomplish.

So here are the actual names of the tables and columns I am working with:

The "ID" that is shared between products in both tables are as follows: - The "ID" column in the "Item" table - The "ItemID" column in the "nitroasl_pamtable" table

What I need to do is go through the "Item" table and find all products that have "SaleType" equals "1"- Then update those IDs in "nitroasl_pamtable" by setting "PAM_SpecialOffer" equal to "1".

Is the following able to do what I want (this is a very rough guess btw)?

UPDATE nitroasl_pamtable
SET PAM_SpecialOffer = SaleType
FROM Item
INNER JOIN nitroasl_pamtable
ON ID = ItemID
WHERE SaleType = 1

I hope that the above makes sense, as I have found it a little hard to put into words, but in a nutshell, I am trying to mark all products with "SaleType=1" with "PAM_SpecialOffer=1" across the two different tables using "ID" and "ItemID" (respectively).

UPDATE

So, the following got me a listing of the set that I want to change. The IDs match up perfectly, etc. Now how can I UPDATE the PAM_SpecialOffer column with "1" (how do I change the following code to do this)?

SELECT i.ID, i.SaleType, i.SaleStartDate, i.SaleEndDate, i.ItemLookupCode, n.ItemID, n.PAM_SpecialOffer
FROM Item AS i
JOIN nitroasl_pamtable AS n
ON i.ID = n.ItemID
WHERE i.SaleType = 1
and (i.SaleStartDate > '2015-01-01' or i.SaleStartDate = '1899-12-31')
and i.SaleEndDate > getdate();

Upvotes: 1

Views: 3187

Answers (2)

Derek Foulk
Derek Foulk

Reputation: 1902

Here is what I ended up with. I do need to mark all products that do not receive the PAM_SpecialOffer = 1 update with PAM_SpecialOffer = NULL, but that should be easy enough!

UPDATE n
SET n.PAM_SpecialOffer = 1
FROM Item AS i
JOIN nitroasl_pamtable AS n
ON i.ID = n.ItemID
WHERE i.SaleType = 1
and (i.SaleStartDate > '2015-01-01' or i.SaleStartDate = '1899-12-31')
and i.SaleEndDate > getdate();

UPDATE - Final Revision

So I made some additional modifications to my query. I am sure there is a better way to write this, but this is the best I could do as far as cleaning up the non-valid 'PAM_SpecialOffer = 1' rows. Tested with a backup of our DB- works great! I'm gonna set this to run every few minutes on our DB.

/* Mark appropriate Sale items as 'PAM_SpecialOffer = 1' */

UPDATE n
SET n.PAM_SpecialOffer = 1
FROM Item AS i
JOIN nitroasl_pamtable AS n
ON i.ID = n.ItemID
WHERE i.SaleType >= 1
and (i.SaleStartDate >= '2015-04-01' or i.SaleStartDate = '1899-12-31')
and i.SaleEndDate >= getdate();


/* Cleanup 'PAM_SpecialOffer' */

UPDATE n
SET n.PAM_SpecialOffer = NULL
FROM Item AS i
JOIN nitroasl_pamtable AS n
ON i.ID = n.ItemID
WHERE i.SaleType < 1
or (i.SaleStartDate < '2015-04-01' and i.SaleStartDate <> '1899-12-31')
or i.SaleEndDate < getdate();

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

It would appear that your statement would work. However, I would write it as:

UPDATE pt
SET PAM_SpecialOffer = i.SaleType
FROM nitroasl_pamtable pt INNER JOIN
     Item i
     ON i.ID = pt.ItemID
WHERE i.SaleType = 1;

The i and pt are table aliases, abbreviations for the table name. In some cases, they are necessary. Here, they just clarify what the query is doing and where the columns are coming from.

As a rule, when I do an update with join, I put the table being updated first in the list of joins.

Upvotes: 1

Related Questions