CODEWITHSUNDEEP
mysqlsql
MambaForever
MambaForever

Reputation: 367

Converting an UPDATE statement to SELECT statement

Ok so I am having a blast this week learning SQL on the fly, I am a front end dev trying to fill in on some database stuff and I know almost no SQL. I need to run an UPDATE and I am having trouble finding the right tutorial to show me exactly how to run something at this scale.

The gist is I need to update the BQMBidQuoteId to match the BQIBidQuoteId for each row as it is a foreign key we are going to use moving forward so we need to update the existing data for our queries. Problem is every way I try it, it updates all the rows with the same Id instead of the unique BQIBidQuoteId's next to it that I need or I just get back "0 rows affected"

Here is my UPDATE statement:

UPDATE BidQuoteMaterials
SET BidQuoteId = x.BidQuoteId
FROM(
Select bqm.BidQuoteItemId, bqi.BidQuoteId BQIBidQuoteId, bqm.BidQuoteId BQMBidQuoteId, bqb.Name, bqm.Product, bqi.Id from BidQuoteMaterials bqm 
    JOIN BidQuoteItems bqi ON bqi.Id = bqm.BidQuoteItemId 
    JOIN BidQuotes bq on bqi.BidQuoteId = bq.Id OR bqm.BidQuoteId = bq.Id 
    JOIN BidQuoteBillables bqb on bqm.Product = bqb.Name 
    AND bq.Id = bqb.BidQuoteId)x WHERE x.BQMBidQuoteId = BidQuoteId

I have also tried: WHERE x.BQMBidQuoteId = Id but I get get back '0 rows affected'

my SELECT statement:

Select bqm.BidQuoteItemId, bqi.BidQuoteId BQIBidQuoteId, bqm.BidQuoteId BQMBidQuoteId, bqb.Name, bqm.Product, bqm.Id BidQuoteMaterialsId from BidQuoteMaterials bqm 
    JOIN BidQuoteItems bqi ON bqi.Id = bqm.BidQuoteItemId 
    JOIN BidQuotes bq on bqi.BidQuoteId = bq.Id OR bqm.BidQuoteId = bq.Id 
    JOIN BidQuoteBillables bqb on bqm.Product = bqb.Name 
    AND bq.Id = bqb.BidQuoteId

Im mostly going off of this UPDATE that I managed to get to work yesterday for another table that we needed to do the was fairly similar:

-- UPDATE BidQuoteConstructionServices
-- SET BidQuoteRateId = x.bidRateId
-- FROM(
--     SELECT bqcs.Id as conId, bq.Id as bidId, bqr.Id as BidRateId
--     FROM BidQuoteConstructionServices bqcs
--     JOIN BidQuoteItems bqi on bqi.Id = bqcs.BidQuoteItemId
--     JOIN BidQuotes bq on bqi.BidQuoteId = bq.Id
--     JOIN BidQuoteRates bqr on bqr.BidQuoteId = bq.Id AND bqr.Name = bqcs.Type
--     Where FieldHours = DailyHours OR DailyHours is NULL)x 
-- WHERE x.conId = Id

Upvotes: 1

Views: 89

Answers (1)

Dimi
Dimi

Reputation: 472

You can try this statement:

UPDATE BidQuoteMaterials
SET BidQuoteMaterials.BidQuoteId = bqi.BidQuoteId
FROM BidQuoteMaterials bqm 
    JOIN BidQuoteItems bqi ON bqi.Id = bqm.BidQuoteItemId 
    JOIN BidQuotes bq on bqi.BidQuoteId = bq.Id OR bqm.BidQuoteId = bq.Id 
    JOIN BidQuoteBillables bqb on bqm.Product = bqb.Name 
    AND bq.Id = bqb.BidQuoteId

Note: do you need these joins? You may do - for filtering

    JOIN BidQuotes bq on bqi.BidQuoteId = bq.Id OR bqm.BidQuoteId = bq.Id 
    JOIN BidQuoteBillables bqb on bqm.Product = bqb.Name 
    AND bq.Id = bqb.BidQuoteId

Upvotes: 2

Related Questions